Skip site navigation (1) Skip section navigation (2)

Strange plan in pg 8.1.0

From: Mattias Kregert <mattias(at)kregert(dot)se>
To: pgsql-performance(at)postgresql(dot)org
Subject: Strange plan in pg 8.1.0
Date: 2006-10-30 11:05:07
Message-ID: 200610301205.08087.mattias@kregert.se (view raw or flat)
Thread:
Lists: pgsql-performance
Look at this insane plan:

lucas=# explain analyse select huvudklass,sum(summa) from kor_tjanster left outer join prislist on prislista=listid and tjanst=tjanstid where kor_id in (select id from kor where lista=10484) group by 1;
                                                                 QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------
 HashAggregate  (cost=212892.07..212892.10 rows=2 width=23) (actual time=4056.165..4056.167 rows=2 loops=1)
   ->  Hash IN Join  (cost=102.84..212889.04 rows=607 width=23) (actual time=4032.931..4056.017 rows=31 loops=1)
         Hash Cond: ("outer".kor_id = "inner".id)
         ->  Hash Left Join  (cost=59.66..206763.11 rows=1215336 width=27) (actual time=4.959..3228.550 rows=1216434 loops=1)
               Hash Cond: (("outer".prislista = ("inner".listid)::text) AND ("outer".tjanst = ("inner".tjanstid)::text))
               ->  Seq Scan on kor_tjanster  (cost=0.00..23802.36 rows=1215336 width=26) (actual time=0.032..1257.241 rows=1216434 loops=1)
               ->  Hash  (cost=51.77..51.77 rows=1577 width=29) (actual time=4.898..4.898 rows=1577 loops=1)
                     ->  Seq Scan on prislist  (cost=0.00..51.77 rows=1577 width=29) (actual time=0.034..2.445 rows=1577 loops=1)
         ->  Hash  (cost=41.79..41.79 rows=557 width=4) (actual time=0.185..0.185 rows=29 loops=1)
               ->  Index Scan using kor_lista on kor  (cost=0.00..41.79 rows=557 width=4) (actual time=0.070..0.150 rows=29 loops=1)
                     Index Cond: (lista = 10484)
 Total runtime: 4056.333 ms

I have an index on kor_tjanster(kor_id), an index on prislist(prislist_id), did ANALYZE and all that stuff... but those indexes are not used.

Why does it come up with this strange plan? It does a seqscan of 1.2 million rows and then a join!? Using the index would be much faster...

I expected something like this:
  1. Index Scan using kor_lista on kor (use lista_id 10484 to get a list of kor_id's - 29 rows (expected 557 rows))
  2. Index Scan using kor_id on kor_tjanster (use the kor_id's to get a list of kor_tjanster - 31 rows)
  3. Index Scan using prislist_listid on prislist (use the 31 kor_tjanster rows to find the corresponding 'huvudklass' for each row)
29+31+31=91 index lookups... which is MUCH faster than seq-scanning millions of rows...

I need to speed up this query. How can i make it use the correct index? Any hints?

I have pg 8.1.0, default settings.

/* m */

Responses

pgsql-performance by date

Next:From: Steinar H. GundersonDate: 2006-10-30 12:27:33
Subject: Re: Strange plan in pg 8.1.0
Previous:From: Simon RiggsDate: 2006-10-30 08:18:21
Subject: Re: partitioned table performance

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group