Skip to content

Instantly share code, notes, and snippets.

@VyacheslavMik

VyacheslavMik/index_comparison.sql

Last active April 11, 2019 10:26
Show Gist options
  • Save VyacheslavMik/a2c3b97a3e6f95a8ee3551cb8f932310 to your computer and use it in GitHub Desktop.
Save VyacheslavMik/a2c3b97a3e6f95a8ee3551cb8f932310 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment