1

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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.