Re: Why won't it index scan? - Mailing list pgsql-general

From Bruce Momjian
Subject Re: Why won't it index scan?
Date
Msg-id 200605181609.k4IG9nZ10282@candle.pha.pa.us
Whole thread Raw
In response to Re: Why won't it index scan?  (Peter Kovacs <peter.kovacs@chemaxon.hu>)
List pgsql-general
Peter Kovacs wrote:
> Sorry for the naive question, but: is there a problem with analyze doing
> full table scans? Analyze will not lock anything, will it?

It used to do that, but the read overhead was too great.

---------------------------------------------------------------------------

>
> Peter
>
> Greg Stark wrote:
> > Tom Lane <tgl@sss.pgh.pa.us> writes:
> >
> >
> >> "Ed L." <pgsql@bluepolka.net> writes:
> >>
> >>> So, does this sound like we just happened to get repeatedly
> >>> horribly unrepresentative random samples with stats target at
> >>> 10?  Are we at the mercy of randomness here?  Or is there a
> >>> better preventive procedure we can follow to systematically
> >>> identify this kind of situation?
> >>>
> >> I think the real issue is that stats target 10 is too small for large
> >> tables: the samples are just not large enough to support a decent
> >> numdistinct estimate, which is the critical stat for cases such as this
> >> (ie, estimating the number of hits on a value that's not in the
> >> most-common-values list).
> >>
> >
> > There's been some discussion on -hackers about this area. Sadly the idea of
> > using samples to calculate numdistinct estimates is fundamentally on pretty
> > shaky ground.
> >
> > Whereas a fixed sample size works fine for calculating distribution of values,
> > in order to generate consistent precision for numdistinct estimates the
> > samples will have to be a constant fraction of the table -- and unfortunately
> > a pretty large fraction at that.
> >
> > So sadly I think "at the mercy of randomness" is pretty accurate. You'll have
> > to raise the statistics target as the table grows and I expect you'll
> > eventually run into some downsides of large stats targets.
> >
> > Some better algorithms were posted, but they would require full table scans
> > during analyze, not just samples.
> >
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

--
  Bruce Momjian   http://candle.pha.pa.us
  EnterpriseDB    http://www.enterprisedb.com

  + If your life is a hard drive, Christ can be your backup. +

pgsql-general by date:

Previous
From: Bob Pawley
Date:
Subject: Re: Interface
Next
From: Bruce Momjian
Date:
Subject: Re: statement_timeout and pg_dump