Re: Incorrect estimates on columns - Mailing list pgsql-performance

From Tom Lane
Subject Re: Incorrect estimates on columns
Date
Msg-id 9262.1192646974@sss.pgh.pa.us
Whole thread Raw
In response to Incorrect estimates on columns  (Chris Kratz <chris.kratz@vistashare.com>)
Responses Re: Incorrect estimates on columns
List pgsql-performance
Chris Kratz <chris.kratz@vistashare.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

pgsql-performance by date:

Previous
From: "Scott Marlowe"
Date:
Subject: Re: Vacuum goes worse
Next
From: Chris Kratz
Date:
Subject: Re: Incorrect estimates on columns