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

Re: Idea about estimating selectivity for single-column expressions

From: Josh Berkus <josh(at)agliodbs(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Greg Stark <gsstark(at)mit(dot)edu>, pgsql-hackers(at)postgresql(dot)org
Subject: Re: Idea about estimating selectivity for single-column expressions
Date: 2009-08-19 19:00:36
Message-ID: 4A8C4BD4.4060509@agliodbs.com (view raw or flat)
Thread:
Lists: pgsql-hackers
Tom, Greg, Robert,

Here's my suggestion:

1. First, estimate the cost of the node with a very pessimistic (50%?)
selectivity for the calculation.

2. If the cost hits a certain threshold, then run the calculation
estimation on the histogram.

That way, we avoid the subtransaction and other overhead on very small sets.


also:

> Trying it on the MCVs makes a lot of sense.  I'm not so sure about
> trying it on the histogram entries.  There's no reason to assume that
> those cluster in any way that will be useful.  (For example, suppose
> that I have the numbers 1 through 10,000 in some particular column and
> my expression is col % 100.)

Yes, but for seriously skewed column distributions, the difference in
frequency between the MCV and a sample "random" distribution will be
huge.  And it's precisely those distributions which are currently
failing in the query planner.

-- 
Josh Berkus
PostgreSQL Experts Inc.
www.pgexperts.com

In response to

Responses

pgsql-hackers by date

Next:From: Alvaro HerreraDate: 2009-08-19 19:01:16
Subject: Re: We should Axe /contrib/start-scripts
Previous:From: Alvaro HerreraDate: 2009-08-19 19:00:00
Subject: Re: We should Axe /contrib/start-scripts

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