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

Re: Almost infinite query -> Different Query Plan when changing where clause value

From: lionel duboeuf <lionel(dot)duboeuf(at)boozter(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Re: Almost infinite query -> Different Query Plan when changing where clause value
Date: 2010-02-15 09:52:49
Message-ID: 4B791971.6020804@boozter.com (view raw or flat)
Thread:
Lists: pgsql-performance
See as attachment the "correct" query plan for an other 'user'.
I confirm by executing manual "VACUUM ANALYZE" that the problem is solved.
But what i don't understand is that i would expect autovacuum to do the job.

Lionel



Kevin Grittner a écrit :
> lionel duboeuf <lionel(dot)duboeuf(at)boozter(dot)com> wrote:
>   
>> Thanks kevin for your answer. Here is some additionnal
>> informations attached as files.
>>     
>  
> Could you supply an EXPLAIN ANALYZE of the fast plan as an
> attachment, for comparison?
>  
> Anyway, it looks like at least one big problem is the bad estimate
> on how many rows will be generated by joining to the users5_ table:
>
>   
>> (cost=13.20..1427.83 rows=1 width=24)
>> (actual time=1.374..517.662 rows=122850 loops=1)
>>     
>
> If it had expected 122850 rows to qualify for that join, it probably
> would have picked a different plan.
>  
> I just reread your original email, and I'm not sure I understand
> what you meant regarding VACUUM ANALYZE.  If you run that right
> beforehand, do you still get the slow plan for user 10?
>  
> -Kevin



Attachment: explain_analyse_ok.txt
Description: text/plain (5.8 KB)

In response to

Responses

pgsql-performance by date

Next:From: Yeb HavingaDate: 2010-02-15 11:11:23
Subject: Re: Why primary key index are not using in joining?
Previous:From: Scott MarloweDate: 2010-02-15 09:50:13
Subject: Re: Why primary key index are not using in joining?

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