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

Re: limit clause breaks query planner?

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Guillaume Cottenceau <gc(at)mnc(dot)ch>, Matthew Wakeling <matthew(at)flymine(dot)org>, pgsql-performance(at)postgresql(dot)org
Subject: Re: limit clause breaks query planner?
Date: 2008-09-04 19:54:53
Message-ID: 87wshrn0tu.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-performance
Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> writes:

> Guillaume Cottenceau <gc(at)mnc(dot)ch> writes:
>> It seems to me that if the correlation is 0.99[1], and you're
>> looking for less than 1% of rows, the expected rows may be at the
>> beginning or at the end of the heap?
>
> Right, but if you know the value being searched for, you could then
> estimate where it is in the table by consulting the histogram.
>
> Actually, an even easier hack (which would have the nice property of not
> needing to know the exact value being searched for), would simply use
> the existing cost estimates if the WHERE variables have low correlation
> (meaning the random-locations assumption is probably good), but apply
> some sort of penalty factor if the correlation is high.  

Fwiw this will have all the same problems our existing uses of the correlation
have. That doesn't mean we shouldn't do it but I would expect it to be
improved along with the other uses when we find a better metric.

I did happen to speak to a statistician the other day and was given some terms
to google. I'll investigate and see if I get anything useful.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com
  Ask me about EnterpriseDB's RemoteDBA services!

In response to

pgsql-performance by date

Next:From: UlrichDate: 2008-09-04 20:01:33
Subject: Re: More shared_buffers instead of effective_cache_size?
Previous:From: Scott MarloweDate: 2008-09-04 19:49:05
Subject: Re: More shared_buffers instead of effective_cache_size?

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