On Wed, 27 Apr 2016 13:48:06 +0200 Alban Hertroys <haram...@gmail.com> wrote:
Hi Alban Thanks for chiming in! > Since you're not using age and gender in this (particular) query until the > rows are combined into a result set already, it doesn't make a whole lot of > sense to add them to the index. Moreover, since your'e ordering by created, > I'd at least put that directly after id: > > create index on reports (id, created); The ORDER statement did not attribute much to the total query time, but it makes sense to index this. > In this case, you're using the values in adverse to filter relevant rid's for > the FK join, so you might be better off with the inverse of above index: > create index on report_adverses (adverse, rid); > create index on report_drugs (drug, rid); Hold the phone ... you just cracked the code. I added these two proposed indexes (reversing their order). After purging the OS disk cache, the query runs in (*drum roll*) 120 ms. You managed to speed this up ~40 times. The query plan: Sort (cost=12108.99..12109.83 rows=337 width=41) (actual time=119.517..119.531 rows=448 loops=1) Sort Key: r.created Sort Method: quicksort Memory: 60kB -> Nested Loop (cost=10940.25..12094.84 rows=337 width=41) (actual time=26.473..119.392 rows=448 loops=1) Join Filter: (d.rid = r.id) -> Merge Join (cost=10939.69..11414.84 rows=337 width=29) (actual time=25.624..33.650 rows=448 loops=1) Merge Cond: (d.rid = a.rid) -> Sort (cost=1417.78..1454.02 rows=14496 width=8) (actual time=4.208..4.976 rows=14074 loops=1) Sort Key: d.rid Sort Method: quicksort Memory: 1050kB -> Index Only Scan using report_drugs_drug_rid_idx on report_drugs d (cost=0.44..415.86 rows=14496 width=8) (actual time=0.648..2.236 rows=14200 loops=1) Index Cond: (drug = ANY ('{359,360,361,362,363}'::integer[])) Heap Fetches: 0 -> Sort (cost=9521.91..9721.56 rows=79860 width=21) (actual time=21.410..24.174 rows=76974 loops=1) Sort Key: a.rid Sort Method: quicksort Memory: 6682kB -> Index Only Scan using report_adverses_adverse_rid_idx on report_adverses a (cost=0.56..3019.24 rows=79860 width=21) (actual time=0.916..10.689 rows=76972 loops=1) Index Cond: (adverse = ANY ('{"back pain - nonspecific","nonspecific back pain","back pain"}'::text[])) Heap Fetches: 0 -> Index Only Scan using reports_id_age_gender_created_idx on reports r (cost=0.56..2.01 rows=1 width=20) (actual time=0.190..0.191 rows=1 loops=448) Index Cond: (id = a.rid) Heap Fetches: 0 Planning time: 15.466 ms Execution time: 119.871 ms This is amazing, and the slow down was purely a fact of missing indexes on the sweet spot. You can now clearly see that the above two indexes are used (report_drugs_drug_rid_idx) and (report_adverses_adverse_rid_idx) and that the nested loop is much faster. > Do these tables have a primary key and can that be used here? Only the "reports" table has a PK, the other two don't (only a foreign key to "reports"). > I hope I'm not being superfluous here, but don't forget to vacuum analyze > after creating those indexes. Eventually autovacuum will do that for you, but > you could otherwise be running tests to verify the impact of adding those > indexes before autovacuum gets around to it. I'm becoming the king of manual VACUUM-ing the past few days ... no need to point that out ;) > Finally, perhaps it's more efficient to weed out all unrelated drugs and > adverses before relating them to reports, something like: > > SELECT r.id, r.age, r.gender, r.created, x.adverse, x.drug > FROM reports r > JOIN ( > SELECT d.rid, a.adverse, d.drug > FROM report_drugs d > JOIN report_adverses a ON a.rid = d.rid > WHERE a.adverse = ANY (ARRAY['back pain - nonspecific', 'nonspecific > back pain', 'back pain']) > AND d.drug = ANY (ARRAY[359, 360, 361, 362, 363]) > ) x ON x.rid = r.id > ORDER BY r.created; With the above indexes created, disk cache flushed, this query ran at the exact same speed, the plan: Sort (cost=12108.14..12108.99 rows=337 width=41) (actual time=119.102..119.111 rows=448 loops=1) Sort Key: r.created Sort Method: quicksort Memory: 60kB -> Nested Loop (cost=10940.25..12094.00 rows=337 width=41) (actual time=26.797..118.969 rows=448 loops=1) -> Merge Join (cost=10939.69..11414.84 rows=337 width=29) (actual time=25.899..33.203 rows=448 loops=1) Merge Cond: (d.rid = a.rid) -> Sort (cost=1417.78..1454.02 rows=14496 width=8) (actual time=4.319..4.981 rows=14074 loops=1) Sort Key: d.rid Sort Method: quicksort Memory: 1050kB -> Index Only Scan using report_drugs_drug_rid_idx on report_drugs d (cost=0.44..415.86 rows=14496 width=8) (actual time=0.748..2.369 rows=14200 loops=1) Index Cond: (drug = ANY ('{359,360,361,362,363}'::integer[])) Heap Fetches: 0 -> Sort (cost=9521.91..9721.56 rows=79860 width=21) (actual time=21.573..23.940 rows=76974 loops=1) Sort Key: a.rid Sort Method: quicksort Memory: 6682kB -> Index Only Scan using report_adverses_adverse_rid_idx on report_adverses a (cost=0.56..3019.24 rows=79860 width=21) (actual time=0.922..10.637 rows=76972 loops=1) Index Cond: (adverse = ANY ('{"back pain - nonspecific","nonspecific back pain","back pain"}'::text[])) Heap Fetches: 0 -> Index Only Scan using reports_id_age_gender_created_idx on reports r (cost=0.56..2.01 rows=1 width=20) (actual time=0.190..0.191 rows=1 loops=448) Index Cond: (id = a.rid) Heap Fetches: 0 Planning time: 15.238 ms Execution time: 119.431 ms So your hunch was right, not much difference there. But man ... this query is now flying ... Hat's off to you, sir! > Alban Hertroys Cheers, Tim -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general