pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kate Collins <klcollins@wsicorp.com>
Cc: postgresql news group <pgsql-sql@postgresql.org>
Subject: Re: SQL command speed
Date: Fri, 19 May 2000 17:39:01 -0400
Message-ID: <9901.958772341@sss.pgh.pa.us> (raw)
In-Reply-To: <3925975A.B8880CCD@wsicorp.com>
References: <3924405E.15014E6B@wsicorp.com>
	<25357.958686476@sss.pgh.pa.us>
	<39253FB7.39516F9@wsicorp.com>
	<8221.958752376@sss.pgh.pa.us>
	<3925975A.B8880CCD@wsicorp.com>

Kate Collins <klcollins@wsicorp.com> writes:
> You are correct in your assessment of the nature of the distribution
> of the elements in the item_a column.  Some values return none or a
> few rows.  While other values return many rows (100 or more).  It is
> not an even distribution.  I had never really considered what effect
> this would have on searching before.

Apparently you have at least one value with close to 2000 rows (IIRC,
the rows estimate is basically half the MCV's row count, except in
the special case where the planner can see that you are comparing
against the MCV itself).

If that's a real data value and not a dummy, then of course you can't
replace it by NULL.  In that case I'm kind of stuck for a good answer
for 7.0; we can't do much to distinguish the few-distinct-values case
from the many-distinct-values-with-some-frequency-outliers case until
we have better statistics than 7.0 keeps.  What you could do as a
stopgap is to force the system to use an indexscan against its
judgment, by doing this before issuing the problem query:

	SET enable_seqscan = OFF;

Naturally I recommend keeping this ON most of the time, but sometimes
you just have to get out the blunt instruments ;-)

			regards, tom lane



view thread (12+ messages)

Message-ID: <9901.958772341@sss.pgh.pa.us>
Permalink:  ../9901.958772341@sss.pgh.pa.us/
Also on:    postgresql.org/message-id/9901.958772341@sss.pgh.pa.us

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: tgl@sss.pgh.pa.us, klcollins@wsicorp.com
  Subject: Re: SQL command speed
  In-Reply-To: <9901.958772341@sss.pgh.pa.us>
  References: <3924405E.15014E6B@wsicorp.com> <25357.958686476@sss.pgh.pa.us> <39253FB7.39516F9@wsicorp.com> <8221.958752376@sss.pgh.pa.us> <3925975A.B8880CCD@wsicorp.com> <9901.958772341@sss.pgh.pa.us>

* 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