From: Gregory Stark <stark@enterprisedb.com>
To: radhika@88thstreet.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Query Plan
Date: Thu, 12 Jul 2007 23:14:47 +0100
Message-ID: <874pk96zhk.fsf@oxford.xeocode.com> (raw)
In-Reply-To: <63759.63.118.86.10.1184274660.squirrel@www.88thstreet.com>
References: <63759.63.118.86.10.1184274660.squirrel@www.88thstreet.com>
"Radhika Sambamurti" <radhika@88thstreet.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
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-sql@postgresql.org
Cc: stark@enterprisedb.com, radhika@88thstreet.com
Subject: Re: Query Plan
In-Reply-To: <874pk96zhk.fsf@oxford.xeocode.com>
* 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