From: Mendola Gaetano <mendola@bigfoot.com>
To: josh@agliodbs.com
To: pgsql-performance@postgresql.org
Cc: pgsql-admin@postgresql.org
Subject: Re: [PERFORM] Wrong plan or what ?
Date: Tue, 22 Jul 2003 20:35:45 +0200
Message-ID: <010601c35080$10256cf0$152aa8c0@GMENDOLA2> (raw)
References: <00c101c35074$1e5f5b20$152aa8c0@GMENDOLA2>
<200307221056.14913.josh@agliodbs.com>
"Josh Berkus" <josh@agliodbs.com>
> Gaetano,
>
> > QUERY PLAN
> > Hash Join (cost=265.64..32000.76 rows=40612 width=263) (actual
> > time=11074.21..11134.28 rows=10 loops=1)
> > Hash Cond: ("outer".id_user = "inner".id_user)
> > -> Seq Scan on user_logs ul (cost=0.00..24932.65 rows=1258965
width=48)
> > (actual time=0.02..8530.21 rows=1258966 loops=1)
>
> OK, here's your problem
>
> The planner thinks that you're going to get 40162 rows out of the final
join,
> not 10. If the row estimate was correct, then the Seq Scan would be a
> reasonable plan. But it's not. Here's some steps you can take to clear
> things up for the planner:
>
> 1) Make sure you've VACUUM ANALYZED
> 2) Adjust the following postgresql.conf statistics:
> a) effective_cache_size: increase to 70% of available (not used by other
> processes) RAM.
> b) random_page_cost: decrease, maybe to 2.
> c) default_statistics_target: try increasing to 100
> (warning: this will significantly increase the time required to do
ANALYZE)
>
> Then test again!
No improvement at all,
I pushed default_statistics_target to 1000
but the rows expected are still 40612 :-(
Of course I restarted the postmaster and I vacuumed analyze the DB
Thank you
Gaetano
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-performance@postgresql.org
Cc: mendola@bigfoot.com, josh@agliodbs.com, pgsql-admin@postgresql.org
Subject: Re: [PERFORM] Wrong plan or what ?
In-Reply-To: <010601c35080$10256cf0$152aa8c0@GMENDOLA2>
References: <00c101c35074$1e5f5b20$152aa8c0@GMENDOLA2> <200307221056.14913.josh@agliodbs.com> <010601c35080$10256cf0$152aa8c0@GMENDOLA2>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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