looking for explination of bad estimates

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.74rows=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.524rows=10870 loops=1)              ->  HashAggregate  (cost=476.53..503.76 rows=2178 width=16) (actual
time=49.639..69.748rows=10870 loops=1)                    ->  Seq Scan on software_download  (cost=0.00..377.78
rows=13167width=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