pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Ken Geis <kgeis@speakeasy.org>
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-performance@postgresql.org
Subject: Re: bad estimates
Date: Sat, 30 Aug 2003 00:38:13 -0700
Message-ID: <3F505465.6010504@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.

After digging through planner code, I found that bumping up the sort_mem 
will make the planner prefer a full table scan and hashed aggregation. 
The sort memory is where the hash table is stored.  In the end, the 
query runs in 4.5 minutes, which is reasonable.

I had planned to try Manfred's index correlation patch to see if it 
would give better estimates for an index scan.  The index scan method 
took maybe 6.5x as long, but the estimate was that it would take 1400x 
as long.  I think instead of trying out his patch I might actually work 
on my application!


Ken

view thread (25+ messages)  latest in thread

Message-ID: <3F505465.6010504@speakeasy.org>
Permalink:  ../3F505465.6010504@speakeasy.org/
Also on:    postgresql.org/message-id/3F505465.6010504@speakeasy.org

copy link · copy postgr.es

reply

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: <3F505465.6010504@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