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

Re: WIP: cross column correlation ...

From: PostgreSQL - Hans-Jürgen Schönig <postgres(at)cybertec(dot)at>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Nathan Boley <npboley(at)gmail(dot)com>, pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org>, Boszormenyi Zoltan <zb(at)cybertec(dot)at>
Subject: Re: WIP: cross column correlation ...
Date: 2011-02-23 14:56:59
Message-ID: B987D673-4905-4154-B2D5-C1624368E7E3@cybertec.at (view raw or flat)
Thread:
Lists: pgsql-hackers
On Feb 23, 2011, at 3:46 PM, Robert Haas wrote:

> On Wed, Feb 23, 2011 at 12:50 AM, Nathan Boley <npboley(at)gmail(dot)com> wrote:
>>> Personally, I think the first thing we ought to do is add a real, bona
>>> fide planner hint to override the selectivity calculation manually,
>>> maybe something like this:
>>> 
>>> WHERE (x < 5 AND y = 1) SELECTIVITY (0.1);
>> 
>> If you're going to go that far, why not just collect statistics on
>> that specific predicate?
>> 
>> ie,  ANALYZE SELECTIVITY ON tablename (x, y) WHERE (x < 5 AND y = 1);
>> 
>> Then it won't fall subject to all of the pitfalls that Tom outlines below.
>> 
>> Selectivities are easy to estimate if we know the predicate. They only
>> become hard when they have to work for every possible predicate.
> 
> Fair point.
> 
> -- 
> Robert Haas


basically we got the idea of allowing "expressions" in cross column stuff. i think this can be very useful. it would fix the problem of a query like that:

	SELECT * FROM table WHERE cos(field) = some_number;

this takes a constant fraction of the table which is usually plain wrong as well (and the error tends to multiply inside the plan).
i am just not sure if i have understood all corner cases of that already.
ultimate goal: get it right for join estimates (this is why a syntax extension is definitely needed - you cannot track all of them automatically).

	many thanks,

		hans

--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt, Austria
Web: http://www.postgresql-support.de


In response to

pgsql-hackers by date

Next:From: Alexander KorotkovDate: 2011-02-23 15:00:09
Subject: WIP: collect frequency statistics for arrays
Previous:From: PostgreSQL - Hans-Jürgen SchönigDate: 2011-02-23 14:54:11
Subject: Re: WIP: cross column correlation ...

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