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

Re: Index help

From: A Gilmore <agilmore(at)shaw(dot)ca>
To: operationsengineer1(at)yahoo(dot)com
Cc: Postgresql Mailing list <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Index help
Date: 2005-07-28 06:20:28
Message-ID: 42E8792C.80201@shaw.ca (view raw or flat)
Thread:
Lists: pgsql-novice
operationsengineer1(at)yahoo(dot)com wrote:
> before the experts chime in...  i read that it is
> fastest to sort your where clause statements with the
> least amount of records returned first.
> 
> using this...
> 
> 
>>WHERE appointments.cal_id in ('82')
>>	AND appointments.start_date <= '2005-12-31'
>>	AND appointments.start_date >= '2004-01-01'
>>	AND appointments.modified >= '2005-01-01';
> 
> 
> use pgadmin to query your db 4 times - using a
> different where clause statement each time.
> 
> put the statement that returns the least amount of
> rows first.
> 
> then use pgadmin to query you db 3 times - using a
> different where clause statement (of three remaining)
> each time.
> 
> repeat until you have the clauses in order of the
> least  records returned.
> 
> you still have to think through it, though, since
> records returned can change over time.  also, you'll
> want to verify that fewer records actually corresponds
> with less query time.
> 
> specific to your case,
> 
> 
>>	AND appointments.modified >= '2005-01-01';
> 
> 
> would probably return less records than either
> 
> 
>>	AND appointments.start_date <= '2005-12-31'
>>	AND appointments.start_date >= '2004-01-01'
> 
> 
> and should be placed before them in there where clause
> (if it does return less records in the production
> environment).
> 
> i can't figure out what 
> 
> 
>>WHERE appointments.cal_id in ('82')
> 
> 
> does so i can't comment on it.
> 
> you also may want to google "sql query optimization."
> 
> i hope this information is accurate and i'm interested
> to hear what the real experts have to say on the
> subject.
> 
> 

Switching around the two (start_date)s may help in practice, Ill try 
that out.  In testing, the only condition that wont match everything is 
the (cal_id).

-A Gilmore


In response to

Responses

pgsql-novice by date

Next:From: operationsengineer1Date: 2005-07-28 17:33:09
Subject: Table Design Issue & PGSQL Performance
Previous:From: A GilmoreDate: 2005-07-28 06:09:13
Subject: Re: Index help

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