I have a PostgreSQL query:

SELECT COUNT(*) 
FROM "issues" 
INNER JOIN "assets" ON "issues"."asset_id" = "assets"."id" 
WHERE "assets"."project_id" = 1 
AND "issues"."active" = 't' 
AND "issues"."phase" IN ('open');

which runs in 1057.612ms with the following query plan:

> Aggregate  (cost=104434.64..104434.65 rows=1 width=0) (actual
> time=1057.289..1057.289 rows=1 loops=1)    ->  Merge Join 
> (cost=4449.93..103041.81 rows=557132 width=0) (actual
> time=1011.340..1052.960 rows=23355 loops=1)
>          Merge Cond: (assets.id = issues.asset_id)
>          ->  Sort  (cost=4421.47..4482.58 rows=24444 width=4) (actual time=30.097..35.907 rows=24241 loops=1)
>                Sort Key: assets.id
>                Sort Method: external sort  Disk: 336kB
>                ->  Seq Scan on assets  (cost=0.00..2639.85 rows=24444 width=4) (actual time=0.009..15.827 rows=24241 loops=1)
>                      Filter: (project_id = 581)
>                      Rows Removed by Filter: 54307
>          ->  Index Only Scan using index_issues_on_asset_id_and_active_and_phase_and_severity on issues 
> (cost=0.43..88451.15 rows=1790279 width=4) (actual time=0.019..659.942
> rows=1878712 loops=1)
>                Index Cond: ((active = true) AND (phase = 'open'::text))
>                Filter: active
>                Heap Fetches: 0  Total runtime: 1057.612 ms (14 rows)

If I turn sorting with set enable_sort=off; I get a significant performance boost:

> `Aggregate  (cost=112186.16..112186.17 rows=1 width=0) (actual
> time=85.151..85.151 rows=1 loops=1)    ->  Nested Loop 
> (cost=0.43..110793.33 rows=557132 width=0) (actual time=0.027..80.705
> rows=23355 loops=1)
>          ->  Seq Scan on assets  (cost=0.00..2639.85 rows=24444 width=4) (actual time=0.010..16.213 rows=24241 loops=1)
>                Filter: (project_id = 581)
>                Rows Removed by Filter: 54307
>          ->  Index Only Scan using index_issues_on_asset_id_and_active_and_phase_and_severity on issues 
> (cost=0.43..3.78 rows=64 width=4) (actual time=0.002..0.002 rows=1
> loops=24241)
>                Index Cond: ((asset_id = assets.id) AND (active = true) AND (phase = 'open'::text))
>                Filter: active
>                Heap Fetches: 0  Total runtime: 85.184 ms (10 rows) `

Obviously I want the better performance. How can I get it without turning off sorting? What can I do better? I've analyzed and vacuumed the tables and it speeds things up by milliseconds (but not hundreds of milliseconds)

select version();
PostgreSQL 9.3.10 on x86_64-unknown-linux-gnu, 
compiled by gcc (GCC) 4.8.2 20140120 (Red Hat 4.8.2-16), 64-bit
  • 1
    The first plan was generated with a cold run or after other executions of the same query? – pietrop Nov 16 '16 at 1:48
  • They were both after many executions of the same query. – Quinn Wilson Nov 16 '16 at 2:01
  • 2
    The slow part of your query was not the sort (even though the sort occurred on disk), right? It looks like the slow part was the index-only scan on issues which in the first plan didn't apply the predicate on asset_id but in the second plan did. – mendosi Nov 16 '16 at 3:12
  • 1
    Try rewriting the query: SELECT COUNT(*) FROM "issues", "assets" WHERE "issues"."asset_id" = "assets"."id" AND "assets"."project_id" = 1 AND "issues"."active" = 't' AND "issues"."phase" IN ('open'); – pietrop Nov 16 '16 at 9:56
  • 1
    Your query plans do not match your query text. The plans have a condition of project_id = 581 but your query has something different. – jjanes Nov 17 '16 at 20:55

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.