Odd explain estimate - Mailing list pgsql-performance

Why is pgsql estimating a cost of 100000000 for retire_today in this
query? I analyzed it, and there's nothing very odd about it, other than
it's a temp table.

BTW, I had to set enable_seqscan=false to get this, otherwise it wants
to seqscan ogr_results, which is rather painful since it occupies 350k
pages.

ogr=# explain analyze select distinct stub_id, nodecount, id from (select distinct stub_id, nodecount, o.id, r.stats_id
fromretire_today r, ogr_results o where o.id=r.id) o where exists (select * from ogr_results o2 where
o2.stub_id=o.stub_idand o2.nodecount=o.nodecount and o2.id=o.stats_id); 
                                                                                   QUERY PLAN
                                                        

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Unique  (cost=101349702.99..101350940.01 rows=12370 width=24) (actual time=422568.80..422568.82 rows=1 loops=1)
   ->  Sort  (cost=101349702.99..101350012.25 rows=123702 width=24) (actual time=422568.79..422568.80 rows=1 loops=1)
         Sort Key: stub_id, nodecount, id
         ->  Subquery Scan o  (cost=101323777.30..101339240.00 rows=123702 width=24) (actual time=388142.51..422568.59
rows=1loops=1) 
               Filter: (subplan)
               ->  Unique  (cost=101323777.30..101339240.00 rows=123702 width=24) (actual time=12456.49..13570.23
rows=56546loops=1) 
                     ->  Sort  (cost=101323777.30..101326869.84 rows=1237016 width=24) (actual time=12456.47..12758.86
rows=56546loops=1) 
                           Sort Key: o.stub_id, o.nodecount, o.id, r.stats_id
                           ->  Nested Loop  (cost=100000000.00..101198600.98 rows=1237016 width=24) (actual
time=93.57..11747.10rows=56546 loops=1) 
                                 ->  Seq Scan on retire_today r  (cost=100000000.00..100000001.93 rows=93 width=8)
(actualtime=0.03..1.78 rows=93 loops=1) 
                                 ->  Index Scan using ogr_results__id on ogr_results o  (cost=0.00..12721.90 rows=13301
width=16)(actual time=18.03..118.43 rows=608 loops=93) 
                                       Index Cond: (o.id = "outer".id)
               SubPlan
                 ->  Index Scan using results_id_count on ogr_results o2  (cost=0.00..3.03 rows=1 width=24) (actual
time=7.21..7.21rows=0 loops=56546) 
                       Index Cond: ((stub_id = $0) AND (nodecount = $1))
                       Filter: (id = $2)
 Total runtime: 422591.48 msec
(17 rows)

        Table "pg_temp_2.retire_today"
  Column  |         Type          | Modifiers
----------+-----------------------+-----------
 email    | character varying(64) | not null
 id       | integer               | not null
 stats_id | integer               | not null

ogr=# select * from pg_stats where tablename='retire_today';
 schemaname |  tablename   | attname  | null_frac | avg_width | n_distinct |           most_common_vals           |
            most_common_freqs                  |
                                  histogram_bounds
                                    | correlation  

------------+--------------+----------+-----------+-----------+------------+--------------------------------------+-----------------------------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------
 pg_temp_1  | retire_today | email    |         0 |        23 |         -1 |                                      |
                                               |
{75521.3541@compuserve.com,arlehma@01019freenet.de,dallas.morlock@worldnet.att.net,hugo@mcs.net,jmk_yoko@yahoo.com.au,lenchik@severodvinsk.ru,nilrem@o2.pl,rc55@pochta.ru,seph429@earthlink.net,vitte@frontbone.de,zotxxx@xs4all.nl}
|   0.894781 
 pg_temp_1  | retire_today | id       |         0 |         4 |         -1 |                                      |
                                               |
{17193,153860,220570,315863,351077,382582,405976,413303,418589,423335,424575}
                                                                                                           |
0.17536
 pg_temp_1  | retire_today | stats_id |         0 |         4 |  -0.946237 | {142167,391154,402835,422577,423809} |
{0.0215054,0.0215054,0.0215054,0.0215054,0.0215054}|
{136669,373730,415341,421924,423416,423553,423959,424089,424354,424609,424976}
                                                                                                           |
-0.132419
 pg_temp_2  | retire_today | email    |         0 |        23 |         -1 |                                      |
                                               |
{75521.3541@compuserve.com,arlehma@01019freenet.de,dallas.morlock@worldnet.att.net,hugo@mcs.net,jmk_yoko@yahoo.com.au,lenchik@severodvinsk.ru,nilrem@o2.pl,rc55@pochta.ru,seph429@earthlink.net,vitte@frontbone.de,zotxxx@xs4all.nl}
|   0.894781 
 pg_temp_2  | retire_today | id       |         0 |         4 |         -1 |                                      |
                                               |
{17193,153860,220570,315863,351077,382582,405976,413303,418589,423335,424575}
                                                                                                           |
0.17536
 pg_temp_2  | retire_today | stats_id |         0 |         4 |  -0.946237 | {142167,391154,402835,422577,423809} |
{0.0215054,0.0215054,0.0215054,0.0215054,0.0215054}|
{136669,373730,415341,421924,423416,423553,423959,424089,424354,424609,424976}
                                                                                                           |
-0.132419
(6 rows)

ogr=# select * from pg_class where relname='retire_today';
   relname    | relnamespace |  reltype  | relowner | relam | relfilenode | relpages | reltuples | reltoastrelid |
reltoastidxid| relhasindex | relisshared | relkind | relnatts | relchecks | reltriggers | relukeys | relfkeys | relrefs
|relhasoids | relhaspkey | relhasrules | relhassubclass | relacl  

--------------+--------------+-----------+----------+-------+-------------+----------+-----------+---------------+---------------+-------------+-------------+---------+----------+-----------+-------------+----------+----------+---------+------------+------------+-------------+----------------+--------
 retire_today |        16765 | 636609103 |      101 |     0 |   636609102 |        1 |        93 |             0 |
      0 | f           | f           | r       |        3 |         0 |           0 |        0 |        0 |       0 | f
       | f          | f           | f              |  
 retire_today |    411964549 | 636609142 |      110 |     0 |   636609141 |        1 |        93 |             0 |
      0 | f           | f           | r       |        3 |         0 |           0 |        0 |        0 |       0 | f
       | f          | f           | f              |  
 retire_today |    478929703 | 632973603 |      101 |     0 |   632973602 |        0 |         0 |             0 |
      0 | f           | f           | r       |        3 |         0 |           0 |        0 |        0 |       0 | f
       | f          | f           | f              |  
(3 rows)
--
Jim C. Nasby, Database Consultant                  jim@nasby.net
Member: Triangle Fraternity, Sports Car Club of America
Give your computer some brain candy! www.distributed.net Team #1828

Windows: "Where do you want to go today?"
Linux: "Where do you want to go tomorrow?"
FreeBSD: "Are you guys coming, or what?"