pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feed[Fwd: Re: Slow query with planner row strange estimation]
2+ messages / 1 participants
[nested] [flat]
* [Fwd: Re: Slow query with planner row strange estimation]
@ 2010-07-12 09:03 damien hostin <damien.hostin@axege.com>
2010-07-12 09:04 ` Re: [Fwd: Re: Slow query with planner row strange estimation] damien hostin <damien.hostin@axege.com>
0 siblings, 1 reply; 2+ messages in thread
From: damien hostin @ 2010-07-12 09:03 UTC (permalink / raw)
To: ; +Cc: pgsql-performance
--
HOSTIN Damien - Equipe R&D
Tel:+33(0)4 63 05 95 40
Société Axège
23 rue Saint Simon
63000 Clermont Ferrand
www.axege.com
Robert Haas a écrit :
> On Wed, Jul 7, 2010 at 10:39 AM, damien hostin <damien.hostin@axege.com> wrote:
>
>> Hello again,
>>
>> At last, I check the same query with the same data on my desktop computer.
>> Just after loading the data, the queries were slow, I launch a vaccum
>> analyse which collect good stats on the main table, the query became quick
>> (~200ms). Now 1classic sata disk computer is faster than our little monster
>> server !!
>>
>
> Have you tried running ANALYZE on the production server?
>
> You might also want to try ALTER TABLE ... SET STATISTICS to a large
> value on some of the join columns involved in the query.
>
>
Hello,
Before comparing the test case on the two machines, I run analyse on the
whole and look at pg_stats table to see if change occurs for the
columns. but on the production server the stats never became as good as
on the desktop computer. I set statistic at 10000 on column used by the
join, run analyse which take a 3000000 row sample then look at the
stats. The stats are not as good as on the desktop. Row number is nearly
the same but only 1 or 2 values are found.
The data are not balanced the same way on the two computer :
- Desktop is 12000 rows with 6000 implicated in the query (50%),
- "Production" (actually a dev/test server) is 6 million rows with 6000
implicated in the query (0,1%).
Columns used in the query are nullable, and in the 5994000 other rows
that are not implicated in the query these columns are null.
I don't know if the statistic target is a % or a number of value to
obtain, but event set at max (10000), it didn't managed to collect good
stats (for this particular query).
As I don't know what more to do, my conclusion is that the data need to
be better balanced to allow the analyse gather better stats. But if
there is a way to improve the stats/query with this ugly balanced data,
I'm open to it !
I hope that in real production, data will never be loaded this way. If
this appened we will maybe set enable_nestloop to off, but I don't think
it's a good solution, other query have a chance to get slower.
Thanks for helping
--
HOSTIN Damien - Equipe R&D
Tel:+33(0)4 63 05 95 40
Société Axège
23 rue Saint Simon
63000 Clermont Ferrand
www.axege.com
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: [Fwd: Re: Slow query with planner row strange estimation]
2010-07-12 09:03 [Fwd: Re: Slow query with planner row strange estimation] damien hostin <damien.hostin@axege.com>
@ 2010-07-12 09:04 ` damien hostin <damien.hostin@axege.com>
0 siblings, 0 replies; 2+ messages in thread
From: damien hostin @ 2010-07-12 09:04 UTC (permalink / raw)
To: pgsql-performance
-Ooops sorry for the spam-
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-07-12 09:04 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-07-12 09:03 [Fwd: Re: Slow query with planner row strange estimation] damien hostin <damien.hostin@axege.com>
2010-07-12 09:04 ` damien hostin <damien.hostin@axege.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox