From: | Robert Treat <xzilla(at)users(dot)sourceforge(dot)net> |
---|---|
To: | pgsql-hackers(at)postgresql(dot)org |
Subject: | looking for explination of bad estimates |
Date: | 2006-01-25 16:55:58 |
Message-ID: | 1138208168.1990.33.camel@camel |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
If at the sort step of the first query you expect to return 2178 rows,
why would you expect to return 1 row after self-joining those results
back on to the original table?
rms=# explain analyze
rms-# select * from
rms-# ( SELECT
rms(# host_id, max(mtime) as mtime
rms(# FROM
rms(# software_download
rms(# WHERE
rms(# bds_status_id not in (6,17,18)
rms(# GROUP BY
rms(# host_id, software_binary_id
rms(# ) latest_download
rms-# JOIN software_download using (host_id,mtime);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Merge Join (cost=646.29..1346.89 rows=1 width=97) (actual time=126.815..225.881 rows=10870 loops=1)
Merge Cond: ("outer".mtime = "inner".mtime)
Join Filter: ("inner".host_id = "outer".host_id)
-> Index Scan using sd_rht_1_idx on software_download (cost=0.00..628.95 rows=13416 width=97) (actual time=0.018..28.508 rows=13416 loops=1)
-> Sort (cost=646.29..651.74 rows=2178 width=12) (actual time=126.775..139.811 rows=10870 loops=1)
Sort Key: latest_download.mtime
-> Subquery Scan latest_download (cost=476.53..525.54 rows=2178 width=12) (actual time=49.643..95.524 rows=10870 loops=1)
-> HashAggregate (cost=476.53..503.76 rows=2178 width=16) (actual time=49.639..69.748 rows=10870 loops=1)
-> Seq Scan on software_download (cost=0.00..377.78 rows=13167 width=16) (actual time=0.006..24.266 rows=13167 loops=1)
Filter: ((bds_status_id <> 6) AND (bds_status_id <> 17) AND (bds_status_id <> 18))
Total runtime: 239.806 ms
(11 rows)
Robert Treat
--
Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL
From | Date | Subject | |
---|---|---|---|
Next Message | Larry Rosenman | 2006-01-25 17:00:45 | Re: Cleaning up the INET/CIDR mess |
Previous Message | Tom Lane | 2006-01-25 16:43:42 | Re: Cleaning up the INET/CIDR mess |