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

Re: Query Plan

From: Gregory Stark <stark(at)enterprisedb(dot)com>
To: <radhika(at)88thstreet(dot)com>
Cc: <pgsql-sql(at)postgresql(dot)org>
Subject: Re: Query Plan
Date: 2007-07-12 22:14:47
Message-ID: 874pk96zhk.fsf@oxford.xeocode.com (view raw or flat)
Thread:
Lists: pgsql-sql
"Radhika Sambamurti" <radhika(at)88thstreet(dot)com> writes:

> When I run the query with combination of FirmClearingID & status the run
> times are approx 3700ms.
> But when I add tradedate ie date_trunc('day', tradedate) = '20070703' the
> run time becomes a horrendous 19631.958 ms.

I'm not really able to make heads or tails of your plans without the query.
But any where clause of the form date_trunc('...',col)='...' will always
generate crappy plans. And it looks like you already have expressions of that
form in the view even in the one which you label "without date_trunc".

Instead try to write a where clause that the database can understand the end
points of. Something like (col >= '20070703' and col < '20070704'). That's
something the database can apply a normal index to and also something it can
have a chance at guessing how many rows will fit.

-- 
  Gregory Stark
  EnterpriseDB          http://www.enterprisedb.com


In response to

  • Query Plan at 2007-07-12 21:11:00 from Radhika Sambamurti

pgsql-sql by date

Next:From: Tom LaneDate: 2007-07-12 22:48:31
Subject: Re: Converting from MS Access field aliases
Previous:From: Gregory StarkDate: 2007-07-12 22:00:22
Subject: Re: Converting from MS Access field aliases

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