Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 analyze shows me that plan for both queries is the same (it uses mostly sequential scan except some subqueries).
  • table_B contains only 10168rows.
  • state has character varying(255) type
  • I tried to increase effective_cache_size, work_mem, shared_buffers to 512MB, 32MB, 128MB respectively but it didn't help.
  • I use PostgreSQL 9.1.9.

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:

http://explain.depesz.com/s/RJs

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:

http://explain.depesz.com/s/f8K

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_nestloop, enable_hashjoin (so query will be performed with another plans). So finally i decided to simplify query.

share|improve this question
    
Perhaps table_B.state is not indexed. Can you post the indexes that are you using for the query? –  PHX255 Apr 22 at 7:47
    
table_B.state is indexed and as you can see postgres uses sequential scan for both cases so indeces will not help (i think). –  tiktak Apr 22 at 7:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.