Search Postgresql Archives

Re: Performance woes relating to DISTINCT (I think)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 9/26/05, boinger <boinger@xxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux