From: Ken Geis <kgeis@speakeasy.org>
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-performance@postgresql.org
Subject: Re: bad estimates
Date: Fri, 29 Aug 2003 22:05:18 -0700
Message-ID: <3F50308E.6070100@speakeasy.org> (raw)
In-Reply-To: <20030830025503.GA30691@wolff.to>
References: <20030829025109.GD4373@wolff.to>
<3F4EBF7E.8020602@speakeasy.org>
<20030829030156.GF4373@wolff.to>
<3F4EC1D0.5010503@speakeasy.org>
<20030829033818.GA5577@wolff.to>
<3F4ECC78.7090502@speakeasy.org>
<20030829040519.GA5746@wolff.to>
<3F4ED1DC.50808@speakeasy.org>
<20030829042453.GA5931@wolff.to>
<3F4F0777.8090503@speakeasy.org>
<20030830025503.GA30691@wolff.to>
Bruno Wolff III wrote:
> I haven't come up with any great ideas for this one. It might be interesting
> to compare the explain analyze output from the distinct on query with
> and without seqscans enabled.
Can't do that comparison. Remember, with seqscan it fails. (Oh, and
that nested loops solution I thought was fast actually took 31 minutes
versus 29 for index scan in 7.4b2.)
I ran another query across the same data:
select price_date, count(*) from day_ends group by price_date;
It used a table scan and hashed aggregates, and it ran in 5.5 minutes.
Considering that, pgsql should be able to do the query that I had been
running in a little more time than that. So...
From what I've learned, we want to convince the optimizer to use a
table scan; that's a good thing. I want it to use hashed aggregates,
but I can't convince it to (unless maybe I removed all of the
statistics.) To use group aggregates, it first sorts the results of the
table scan (all 17 million rows!) There ought to be some way to tell
pgsql not to do sorts above a certain size. In this case, if I set
enable_sort=false, it goes back to the index scan. If I then set
enable_indexscan=false, it goes back to sorting.
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: kgeis@speakeasy.org, bruno@wolff.to
Subject: Re: bad estimates
In-Reply-To: <3F50308E.6070100@speakeasy.org>
* 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