pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed

From: Kevin Grittner <Kevin.Grittner@wicourts.gov>
To: theo@flame.co.za
To: pgsql-performance@postgresql.org
Subject: Re: Performance problems with prepared statements
Date: Fri, 12 Oct 2007 14:03:50 -0500
Message-ID: <470F7EC5.EE98.0025.0@wicourts.gov> (raw)
In-Reply-To: <1192201021.6170.47.camel@localhost.localdomain>
References: <1192027540.2495.3.camel@localhost.localdomain>
	<470CE918.4060608@dalibo.com>
	<1192044840.2549.21.camel@fedmac>
	<slrnfgsqpi.27gg.andrew+nonews@atlantis.supernews.net>
	<b42b73150710111304m161814cds984a7c525ff87241@mail.gmail.com>
	<1192174502.2470.9.camel@localhost.localdomain>
	<b42b73150710120602g2f47dd21lb851a72d05a184f1@mail.gmail.com>
	<1192201021.6170.47.camel@localhost.localdomain>

>>> On Fri, Oct 12, 2007 at  9:57 AM, in message
<camel@localhost.localdomain/">1192201021.6170.47.camel@localhost.localdomain>, Theo Kramer
<theo@flame.co.za> wrote: 
> 
> select * from foo where
>   (a = a1 and b = b1 and c >= c1) or
>   (a = a1 and b < b1) or
>   (a > a1)
> order by a, b desc, c;
> 
> I have, however, found that transforming the above into a union based
> query performs substantially better.
 
Another approach which often performs better is to rearrange the logic
so that the high-order predicate is AND instead of OR:
 
select * from foo where
      ( a >= a1
  and ( a >  a1
   or ( b <= b1
  and ( b <  b1
   or ( c >= c1 )))))
  order by a, b desc, c;
 
With the right index and a limit on rows, this can do particularly well.
 
-Kevin
 

view thread (18+ messages)

Message-ID: <470F7EC5.EE98.0025.0@wicourts.gov>
Permalink:  ../470F7EC5.EE98.0025.0@wicourts.gov/
Also on:    postgresql.org/message-id/470F7EC5.EE98.0025.0@wicourts.gov

copy link · copy postgr.es

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: Kevin.Grittner@wicourts.gov, theo@flame.co.za
  Subject: Re: Performance problems with prepared statements
  In-Reply-To: <470F7EC5.EE98.0025.0@wicourts.gov>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox