Skip site navigation (1) Skip section navigation (2)

Re: pgsql: When estimating the selectivity of an inequality "column >

From: Greg Stark <gsstark(at)mit(dot)edu>
To: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
Cc: Tom Lane <tgl(at)postgresql(dot)org>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: pgsql: When estimating the selectivity of an inequality "column >
Date: 2010-01-04 15:50:03
Message-ID: 407d949e1001040750t5fc5b52ctf6f5233b03413617@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-committerspgsql-hackers
On Mon, Jan 4, 2010 at 2:50 PM, Alvaro Herrera
<alvherre(at)commandprompt(dot)com> wrote:
> Maybe autovac could run such a cheap ANALYZE frequently on tables with
> large number of inserts (but not large enough to trigger a regular
> ANALYZE) ... say a fixed number of tuples (not depending on pg_class.reltuples)
>

Well that might cut down on the number of plans that need to do it
themselves. But I'm more concerned about a database that *doesn't*
have a frequent number of inserts. Such a database should presumably
trigger a real analyze reasonably quickly.

But consider a database that has one new record inserted per day but
thousands of queries per minute looking up the maximum value in the
table. This change has basically doubled the work that query needs to
do since the planner now needs to do the same lookup that the query
itself was going to do. And autovacuum won't fire for a long long time
against this table.

Admittedly the fact that there is 100% overhead isn't terribly
interesting since it's really a fixed overhead and only 100% if that
query happens to be correspondingly cheap. But it's still annoying to
me that we'll potentially never figure out what the new stats should
be and stop doing the lookup no matter how long the new row sits there
unless some unrelated activity triggers a real analyze.



-- 
greg

In response to

pgsql-hackers by date

Next:From: Magnus HaganderDate: 2010-01-04 15:57:28
Subject: Re: Setting oom_adj on linux?
Previous:From: Csaba NagyDate: 2010-01-04 15:48:14
Subject: Re: Thoughts on statistics for continuously advancing columns

pgsql-committers by date

Next:From: Tom LaneDate: 2010-01-04 16:34:11
Subject: pgsql: Improve PGXS makefile system to allow the module's makefile to
Previous:From: Alvaro HerreraDate: 2010-01-04 14:50:21
Subject: Re: pgsql: When estimating the selectivity of an inequality "column >

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group