From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Michael McFarland <sidlonDoesntLikeSpam@yahoo.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: adding 'limit' leads to very slow query
Date: Mon, 14 Mar 2005 06:50:21 -0800 (PST)
Message-ID: <20050314064241.V6400@megazone.bigpanda.com> (raw)
In-Reply-To: <opsndn2ujisvs6tg@localhost.localdomain>
References: <opsnaj0hctsvs6tg@localhost.localdomain>
<20050307230123.L97147@megazone.bigpanda.com>
<opsndn2ujisvs6tg@localhost.localdomain>
On Wed, 9 Mar 2005, Michael McFarland wrote:
> I continue to be stumped by this. You are right that I should have
> listed the estimates provided by explain... basically for the select where
> bar = 41, it's estimating there will be 40,000 rows instead of 7, out of
> what's actuallly 5 million records in the table.
>
> So far I've tried increase statistics for the bar column from the
> default 10 to 100 (vacuum analyzing after) and the explain-plan hasn't
> changed. I also notice that afterward, the pg_stats record for the bar
Did the estimates change at all?
> column still only lists the top 5 values of bar (out of 68 unique values
> in the table). Are there any other settings I could try to improve the
> detail of the statistics?
Well, I'd first try moving up to a statistic target of 1000 in
order to try sampling a greater number of rows. I'd wonder if there's
enough difference in frequency that it's just not visiting any with the
other values. I'm not sure that it'll help that much though; hopefully
someone else will have an idea.
> By the way, I think I do have a workaround for this particular query:
> select * from (select * from foo where barId = 412 order by id
> desc) as tempview limit 25;
> This query uses the bar index and completes instantly. However, I feel
> like I should find the heart of the problem, since bad statistics could
> end up affecting other plans, right?
Yeah, it's best to get it to estimate somewhat reasonably before looking
for workarounds.
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 using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: sszabo@megazone.bigpanda.com, sidlonDoesntLikeSpam@yahoo.com
Subject: Re: adding 'limit' leads to very slow query
In-Reply-To: <20050314064241.V6400@megazone.bigpanda.com>
* 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