Skip site navigation (1) Skip section navigation (2)

Re: [PERFORM] Wrong plan or what ?

From: "Mendola Gaetano" <mendola(at)bigfoot(dot)com>
To: <josh(at)agliodbs(dot)com>, <pgsql-performance(at)postgresql(dot)org>
Cc: <pgsql-admin(at)postgresql(dot)org>
Subject: Re: [PERFORM] Wrong plan or what ?
Date: 2003-07-22 18:35:45
Message-ID: 010601c35080$10256cf0$152aa8c0@GMENDOLA2 (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-performance
"Josh Berkus" <josh(at)agliodbs(dot)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








In response to

pgsql-performance by date

Next:From: Bruce MomjianDate: 2003-07-22 18:50:37
Subject: Re: Dual Xeon + HW RAID question
Previous:From: Vivek KheraDate: 2003-07-22 18:18:15
Subject: Re: Tunning FreeeBSD and PostgreSQL

pgsql-admin by date

Next:From: Andrew SullivanDate: 2003-07-22 19:32:03
Subject: Re: Replication/Failover/HA solution
Previous:From: Mendola GaetanoDate: 2003-07-22 17:56:30
Subject: Re: [PERFORM] Wrong plan or what ?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group