Re: Performance woes relating to DISTINCT (I think)

On 9/26/05, boinger <boinger(at)gmail(dot)com> wrote:
>
> Hello.
>
> I'm not sure if this is a question suited for here, the -sql list, or
> the -performance list, so if I'm mis-posting, please direct me to the
> right list.
>
> I was unable to come up with anything relevant from the archives
> (though...I am not too sure where to start so I may just have been
> looking for the wrong things).
>
> I am trying to convert a bunch of code from MySQL to Postgresql. In
> MySQL I was running this particular query with more complexity (with
> joins and such) and it takes far less than a second. The Pg
> stripped-down version takes over 45 seconds (it was taking over 80
> seconds with the joins).

QUERY PLAN
> -> GroupAggregate (cost=0.00..85168.65 rows=11 width=22)
> (actual time=3149.916..45578.292 rows=515 loops=1)

Hmm, planner expected 11 rows, got 515

(cost=0.00..85167.23 rows=107 width=22) (actual
> time=3144.908..45366.147 rows=29893 loops=1)

planner expected 107 rows, got 29893...
I guess the problem here is that planner has wrong idea how your
data looks. Try doing two things:

VACUUM ANALYZE;
(of tables in question or whole database)

If that doesn't help, do increase the statistics target. By default
PostgreSQL
keeps 10 samples, but you might want to increase it to 50 or even 100.
And then rerun VACUUM ANALYZE.

If it doesn't help -- please repost the new query plan once again.

Regards,
Dawid