Have the following query:
SELECT * FROM table_A INNER JOIN table_B ON /* conditions */ INNER JOIN table_C ON /* conditions */ INNER JOIN table_D ON /* conditions */ WHERE table_B.state = 'active' AND table_B.completed = 'f' AND table_B.end_date IS NOT NULL AND /* tons of other conditions and subqueries*/ ORDER BY table_A.id
It takes 250'000 ms. If execute this query without
table_B.state = 'active' condition, it takes less than a second.
explain analyzeshows me that plan for both queries is the same (it uses mostly sequential scan except some subqueries).
- I tried to increase
shared_buffersto 512MB, 32MB, 128MB respectively but it didn't help.
- I use
I really can't believe that string comparison costs so much and think that it's not a reason. Any suggestions?
Below you can see part of
explain analyze output for both queries. For 250.000 ms:
Sort (cost=5966.80..5966.80 rows=1 width=37) (actual time=250432.738..250432.747 rows=95 loops=1) Sort Key: table_A.id Sort Method: quicksort Memory: 32kB -> Nested Loop (cost=0.00..5966.79 rows=1 width=37) (actual time=685.612..250432.564 rows=95 loops=1) -> Nested Loop (cost=0.00..5965.76 rows=1 width=41) (actual time=685.594..250431.181 rows=95 loops=1) Join Filter: some conditions -> Nested Loop (cost=0.00..342.66 rows=114 width=20) (actual time=0.056..10.305 rows=1575 loops=1) Join Filter: some conditions -> Seq Scan on table_B (cost=0.00..321.10 rows=454 width=24) (actual time=0.036..6.091 rows=1618 loops=1) Filter: ((NOT completed) AND (end_date IS NOT NULL) AND ((state)::text = 'active'::text))
for 768 ms:
Sort (cost=33164.70..33164.72 rows=8 width=3223) (actual time=701.824..701.833 rows=112 loops=1) Sort Key: table_A.id Sort Method: quicksort Memory: 142kB -> Nested Loop (cost=0.00..33164.58 rows=8 width=3223) (actual time=682.709..701.456 rows=112 loops=1) -> Nested Loop (cost=0.00..33161.36 rows=8 width=3204) (actual time=682.691..701.098 rows=112 loops=1) Join Filter: some conditions -> Nested Loop (cost=0.00..422.31 rows=697 width=377) (actual time=0.047..3.951 rows=2009 loops=1) Join Filter: some conditions -> Seq Scan on table_B (cost=0.00..295.68 rows=2789 width=168) (actual time=0.011..1.897 rows=2052 loops=1) Filter: ((NOT completed) AND (end_date IS NOT NULL))
UPDATE: I think
Postgres incorrectly choose execution plan for queries. As you can see estimation of query execution significantly differs from real execution. Also this queries are performed with different performance on different
Postgres versions. For example, for
9.1.13 it takes
1.6s for both queries. For
9.3.4 they both are performed for
280s. Also it's possible to increase performance if turn off
enable_hashjoin (so query will be performed with another plans). So finally i decided to simplify query.