index_comparison.sql

CREATE INDEX idxprcgiven ON practitioner USING gin ((resource#>>'{name,0,given}') gin_trgm_ops);
usnpi=# EXPLAIN ANALYZE SELECT id, resource FROM practitioner WHERE (deleted = FALSE AND resource#>>'{name,0,family}' ilike 'Fox%') ORDER BY resource#>>'{name,0,family}';
                                                           QUERY PLAN                                                            
---------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=426.35..427.32 rows=386 width=949) (actual time=533.607..534.258 rows=2848 loops=1)
   Sort Key: ((resource #>> '{name,0,family}'::text[]))
   Sort Method: quicksort  Memory: 3793kB
   ->  Bitmap Heap Scan on practitioner  (cost=16.00..409.77 rows=386 width=949) (actual time=3.016..529.767 rows=2848 loops=1)
         Recheck Cond: ((resource #>> '{name,0,family}'::text[]) ~~* 'Fox%'::text)
         Rows Removed by Index Recheck: 86
         Filter: (NOT deleted)
         Rows Removed by Filter: 4
         Heap Blocks: exact=2926
         ->  Bitmap Index Scan on idxprcfamily  (cost=0.00..15.90 rows=387 width=0) (actual time=2.546..2.546 rows=2938 loops=1)
               Index Cond: ((resource #>> '{name,0,family}'::text[]) ~~* 'Fox%'::text)
 Planning time: 0.656 ms
 Execution time: 534.866 ms
(13 rows)
 practitioner      | idxprcgiven              | 4.47648e+06 | 4317 MB    | 126 MB     | N      |               0 |           0 |              0


CREATE INDEX idxprcgiven ON practitioner USING gist ((resource#>>'{name,0,given}') gist_trgm_ops);
usnpi=# EXPLAIN ANALYZE SELECT id, resource FROM practitioner WHERE (deleted = FALSE AND resource#>>'{name,0,family}' ilike 'Fox%') ORDER BY resource#>>'{name,0,family}';
                                                             QUERY PLAN                                                              
-------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=45578.03..45684.69 rows=42664 width=947) (actual time=411.719..412.254 rows=2848 loops=1)
   Sort Key: ((resource #>> '{name,0,family}'::text[]))
   Sort Method: quicksort  Memory: 3793kB
   ->  Bitmap Heap Scan on practitioner  (cost=356.56..42297.01 rows=42664 width=947) (actual time=3.611..407.962 rows=2848 loops=1)
         Recheck Cond: ((resource #>> '{name,0,family}'::text[]) ~~* 'Fox%'::text)
         Rows Removed by Index Recheck: 86
         Filter: (NOT deleted)
         Rows Removed by Filter: 4
         Heap Blocks: exact=2926
         ->  Bitmap Index Scan on idxprcfamily  (cost=0.00..345.90 rows=42786 width=0) (actual time=3.099..3.099 rows=2938 loops=1)
               Index Cond: ((resource #>> '{name,0,family}'::text[]) ~~* 'Fox%'::text)
 Planning time: 0.946 ms
 Execution time: 412.806 ms
(13 rows)
 practitioner      | idxprcgiven              | 4.48569e+06 | 4317 MB    | 317 MB     | N      |               0 |           0 |              0


CREATE INDEX idxprcspec ON practitioner ((resource#>>'{qualification,0,code,coding,0,code}'));
usnpi=# EXPLAIN ANALYZE SELECT id, resource FROM practitioner WHERE (deleted = FALSE AND resource#>>'{qualification,0,code,coding,0,code}' in ('207L00000X','207LA0401X','207LC0200X','207LH0002X','207LP2900X','207LP3000X')) ORDER BY resource#>>'{name,0,family}';
                                                                                QUERY PLAN                                                                                 
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Sort  (cost=64647.54..64801.22 rows=61472 width=946) (actual time=141967.063..141988.351 rows=56359 loops=1)
   Sort Key: ((resource #>> '{name,0,family}'::text[]))
   Sort Method: quicksort  Memory: 88918kB
   ->  Index Scan using idxprcspec on practitioner  (cost=0.43..59758.16 rows=61472 width=946) (actual time=12.697..138016.137 rows=56359 loops=1)
         Index Cond: ((resource #>> '{qualification,0,code,coding,0,code}'::text[]) = ANY ('{207L00000X,207LA0401X,207LC0200X,207LH0002X,207LP2900X,207LP3000X}'::text[]))
         Filter: (NOT deleted)
         Rows Removed by Filter: 170
 Planning time: 0.719 ms
 Execution time: 142019.496 ms
(9 rows)
 practitioner      | idxprcspec               | 4.48356e+06 | 4317 MB    | 135 MB     | N      |              32 |      519847 |          56581



CREATE INDEX idxprcspec ON practitioner USING gin ((resource#>>'{qualification,0,code,coding,0,code}') gin_trgm_ops);
usnpi=# EXPLAIN ANALYZE SELECT id, resource FROM practitioner WHERE (deleted = FALSE AND resource#>>'{qualification,0,code,coding,0,code}' in ('207L00000X','207LA0401X','207LC0200X','207LH0002X','207LP2900X','207LP3000X')) ORDER BY resource#>>'{name,0,family}';
                                                                                           QUERY PLAN                                                                                            
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Merge  (cost=592774.79..598540.16 rows=49414 width=948) (actual time=123508.003..123603.821 rows=56359 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=591774.77..591836.54 rows=24707 width=948) (actual time=123446.858..123458.752 rows=18786 loops=3)
         Sort Key: ((resource #>> '{name,0,family}'::text[]))
         Sort Method: quicksort  Memory: 29952kB
         ->  Parallel Seq Scan on practitioner  (cost=0.00..589972.07 rows=24707 width=948) (actual time=28.810..119619.110 rows=18786 loops=3)
               Filter: ((NOT deleted) AND ((resource #>> '{qualification,0,code,coding,0,code}'::text[]) = ANY ('{207L00000X,207LA0401X,207LC0200X,207LH0002X,207LP2900X,207LP3000X}'::text[])))
               Rows Removed by Filter: 1473374
 Planning time: 0.683 ms
 Execution time: 123607.800 ms
(11 rows)
 practitioner      | idxprcspec               | 4.47424e+06 | 4317 MB    | 80 MB      | N      |               0 |           0 |              0
                                                                                                                
                                                                                                                CREATE INDEX idxprcspec ON practitioner USING gist ((resource#>>'{qualification,0,code,coding,0,code}') gist_trgm_ops);
usnpi=# EXPLAIN ANALYZE SELECT id, resource FROM practitioner WHERE (deleted = FALSE AND resource#>>'{qualification,0,code,coding,0,code}' in ('207L00000X','207LA0401X','207LC0200X','207LH0002X','207LP2900X','207LP3000X')) ORDER BY resource#>>'{name,0,family}';
                                                                                           QUERY PLAN                                                                                            
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Gather Merge  (cost=592809.46..598518.82 rows=48934 width=946) (actual time=106961.627..107044.847 rows=56359 loops=1)
   Workers Planned: 2
   Workers Launched: 2
   ->  Sort  (cost=591809.43..591870.60 rows=24467 width=946) (actual time=106864.834..106875.501 rows=18786 loops=3)
         Sort Key: ((resource #>> '{name,0,family}'::text[]))
         Sort Method: quicksort  Memory: 29739kB
         ->  Parallel Seq Scan on practitioner  (cost=0.00..590025.97 rows=24467 width=946) (actual time=23.360..104350.035 rows=18786 loops=3)
               Filter: ((NOT deleted) AND ((resource #>> '{qualification,0,code,coding,0,code}'::text[]) = ANY ('{207L00000X,207LA0401X,207LC0200X,207LH0002X,207LP2900X,207LP3000X}'::text[])))
               Rows Removed by Filter: 1473374
 Planning time: 0.722 ms
 Execution time: 107048.854 ms
(11 rows)
 practitioner      | idxprcspec               | 4.48078e+06 | 4317 MB    | 313 MB     | N      |               0 |           0 |              0