Re: [HACKERS] Recognizing range constraints (was Re: [PERFORM] Plan for relatively simple query seems to be very inefficient)

On Wed, Apr 06, 2005 at 06:35:10PM -0400, Tom Lane wrote: > "Jim C. Nasby" <[EMAIL PROTECTED]> writes: > > On Wed, Apr 06, 2005 at 06:09:37PM -0400, Tom Lane wrote: > >> Can anyone suggest a more general rule? Do we need for example to > >> consider whether the relation membership is the same in t

Re: [HACKERS] Recognizing range constraints (was Re: [PERFORM] Plan for relatively simple query seems to be very inefficient)

John A Meinel <[EMAIL PROTECTED]> writes: > Actually, I think he was saying do a nested loop, and for each item in > the nested loop, re-evaluate if an index or a sequential scan is more > efficient. > I don't think postgres re-plans once it has started, though you could > test this in a plpgsql f

Re: [HACKERS] Recognizing range constraints (was Re: [PERFORM] Plan for relatively simple query seems to be very inefficient)

"Jim C. Nasby" <[EMAIL PROTECTED]> writes: > On Wed, Apr 06, 2005 at 06:09:37PM -0400, Tom Lane wrote: >> Can anyone suggest a more general rule? Do we need for example to >> consider whether the relation membership is the same in two clauses >> that might be opposite sides of a range restriction?

Re: [HACKERS] Recognizing range constraints (was Re: [PERFORM] Plan for relatively simple query seems to be very inefficient)

On Wed, Apr 06, 2005 at 06:09:37PM -0400, Tom Lane wrote: > Can anyone suggest a more general rule? Do we need for example to > consider whether the relation membership is the same in two clauses > that might be opposite sides of a range restriction? It seems like > > a.x > b.y AND a.x < b

Recognizing range constraints (was Re: [PERFORM] Plan for relatively simple query seems to be very inefficient)

I wrote: > Arjen van der Meijden <[EMAIL PROTECTED]> writes: >> SELECT COUNT(*) FROM >> data_main AS dm, >> postcodes AS p >> WHERE dm.range BETWEEN p.range_from AND p.range_till > Planner error ... because it doesn't have any good way to estimate the > number of matching rows, it thinks that way

Re: [PERFORM] Plan for relatively simple query seems to be very inefficient

Arjen van der Meijden <[EMAIL PROTECTED]> writes: > On 6-4-2005 19:42, Tom Lane wrote: >> Wrong index ... what you probably could use here is an index on >> data_main.range, so that the query could run with postcodes as the >> outer side. I get such a plan by default with empty tables: >> but I'm

Re: [PERFORM] Plan for relatively simple query seems to be very inefficient

Arjen van der Meijden <[EMAIL PROTECTED]> writes: > On 6-4-2005 20:09, Tom Lane wrote: >> Comparing the nestloop case to the hash case does make one think that >> there's an awful lot of overhead somewhere, though. Two int2 >> comparisons ought not take very long :-(. Arjen, are you interested >>

Re: [PERFORM] Plan for relatively simple query seems to be very inefficient

Quoting Arjen van der Meijden <[EMAIL PROTECTED]>: > Hi list, > > I noticed on a forum a query taking a surprisingly large amount of time > in MySQL. Of course I wanted to prove PostgreSQL 8.0.1 could do it much > better. To my surprise PostgreSQL was ten times worse on the same > machine! And

Re: [PERFORM] Plan for relatively simple query seems to be very inefficient

On 6-4-2005 19:42, Tom Lane wrote: Arjen van der Meijden <[EMAIL PROTECTED]> writes: I noticed on a forum a query taking a surprisingly large amount of time in MySQL. Of course I wanted to prove PostgreSQL 8.0.1 could do it much better. To my surprise PostgreSQL was ten times worse on the same m

Re: [PERFORM] Plan for relatively simple query seems to be very inefficient

"Dave Held" <[EMAIL PROTECTED]> writes: > My completely amateur guess is that the planner is able to use > Merge Join and Hash Join on your contrived queries because you > are only trying to join one field to a single value (i.e.: > operator=). But the BETWEEN clause is what forces the Nested > Lo

Re: [PERFORM] Plan for relatively simple query seems to be very inefficient

Arjen van der Meijden <[EMAIL PROTECTED]> writes: > I noticed on a forum a query taking a surprisingly large amount of time > in MySQL. Of course I wanted to prove PostgreSQL 8.0.1 could do it much > better. To my surprise PostgreSQL was ten times worse on the same > machine! And I don't underst

Re: [PERFORM] Plan for relatively simple query seems to be very inefficient

On 6-4-2005 19:04, Steve Atkins wrote: On Wed, Apr 06, 2005 at 06:52:35PM +0200, Arjen van der Meijden wrote: Hi list, I noticed on a forum a query taking a surprisingly large amount of time in MySQL. Of course I wanted to prove PostgreSQL 8.0.1 could do it much better. To my surprise PostgreSQL

Re: [PERFORM] Plan for relatively simple query seems to be very inefficient

> -Original Message- > From: Arjen van der Meijden > [mailto:[EMAIL PROTECTED] > Sent: Wednesday, April 06, 2005 11:53 AM > To: performance pgsql > Subject: [PERFORM] Plan for relatively simple query seems to be very > inefficient > > [...] > SELECT COUNT(*) FROM > data_main AS dm, > post

Re: [PERFORM] Plan for relatively simple query seems to be very inefficient

On Wed, Apr 06, 2005 at 06:52:35PM +0200, Arjen van der Meijden wrote: > Hi list, > > I noticed on a forum a query taking a surprisingly large amount of time > in MySQL. Of course I wanted to prove PostgreSQL 8.0.1 could do it much > better. To my surprise PostgreSQL was ten times worse on the s