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
