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 from retire_today r, ogr_results o where 
o.id=r.id) o where exists (select * from ogr_results o2 where o2.stub_id=o.stub_id and 
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=1 loops=1)
               Filter: (subplan)
               ->  Unique  (cost=101323777.30..101339240.00 rows=123702 width=24) 
(actual time=12456.49..13570.23 rows=56546 loops=1)
                     ->  Sort  (cost=101323777.30..101326869.84 rows=1237016 width=24) 
(actual time=12456.47..12758.86 rows=56546 loops=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.10 rows=56546 loops=1)
                                 ->  Seq Scan on retire_today r  
(cost=100000000.00..100000001.93 rows=93 width=8) (actual time=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.21 rows=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 |           
                           |                                                     | 
[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL 
PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL 
PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED] |    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 |           
                           |                                                     | 
[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL 
PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED],[EMAIL 
PROTECTED],[EMAIL PROTECTED],[EMAIL PROTECTED] |    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                  [EMAIL PROTECTED]
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?"

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

Reply via email to