Tom Lane <[EMAIL PROTECTED]> writes: > Klint Gore <[EMAIL PROTECTED]> writes: >> On Tue, 29 May 2007 17:16:57 -0700, "Tyrrill, Ed" <[EMAIL PROTECTED]> wrote: >>> mdsdb=# explain analyze select backupobjects.record_id from >>> backupobjects left outer join backup_location using(record_id) where >>> backup_id = 1071; > >> Why are you using left join? >> The where condition is going to force the row to exist.
This select is a simplified version of what I am really doing that still exhibits the problem I am having. I know this small query doesn't really make sense, but I thought it would be easier to evaluate something small rather then the entire query. > > Which indeed the planner figured out (note the lack of any mention of > left join in the EXPLAIN result). Michael put his finger on the problem > though: there's something way off about the rowcount estimate here: > >> -> Bitmap Heap Scan on backup_location (cost=3831.20..360207.21 >> rows=436915 width=8) (actual time=94.375..97.688 rows=2789 loops=1) >> Recheck Cond: (backup_id = 1071) >> -> Bitmap Index Scan on backup_location_bid >> (cost=0.00..3831.20 rows=436915 width=0) (actual time=84.239..84.239 >> rows=2789 loops=1) >> Index Cond: (backup_id = 1071) > > With such a simple index condition the planner really ought to be able > to come close to the right rowcount estimate. Check for vacuuming > problems, check for lack of ANALYZE, consider whether you need to bump > up the statistics target ... > > regards, tom lane I did a vacuum analyze after inserting all the data. Is there possibly a bug in analyze in 8.1.5-6? I know it says rows=436915, but the last time the backup_location table has had that little data in it was a couple months ago, and analyze has been run many times since then. Currently it has over 160 million rows. Thanks, Ed ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
