From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Chris Kratz <chris(dot)kratz(at)vistashare(dot)com> |
Cc: | Postgresql Performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: Incorrect estimates on columns |
Date: | 2007-10-17 18:49:34 |
Message-ID: | 9262.1192646974@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
Chris Kratz <chris(dot)kratz(at)vistashare(dot)com> writes:
> I'm struggling to get postgres to run a particular query quickly.
The key problem seems to be the join size misestimate here:
> -> Hash Join (cost=45.92..1251.07 rows=21 width=8) (actual time=0.948..10.439 rows=1199 loops=1)
> Hash Cond: (par.activity = a.activityid)
> -> Bitmap Heap Scan on rpt_agencyquestioncache_171_0 par (cost=21.92..1222.19 rows=1247 width=8) (actual time=0.415..3.081 rows=1199 loops=1)
> -> Hash (cost=19.21..19.21 rows=383 width=4) (actual time=0.513..0.513 rows=383 loops=1)
Evidently it's not realizing that every row of par will have a join
partner, but why not? I suppose a.activityid is unique, and in most
cases that I've seen the code seems to get that case right.
Would you show us the pg_stats rows for par.activity and a.activityid?
regards, tom lane
From | Date | Subject | |
---|---|---|---|
Next Message | Chris Kratz | 2007-10-17 19:43:40 | Re: Incorrect estimates on columns |
Previous Message | Scott Marlowe | 2007-10-17 18:43:57 | Re: Vacuum goes worse |