From: Brian Cox <brian.cox@ca.com>
To: Robert Haas [robertmhaas@gmail.com] <robertmhaas@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: query looping?
Date: Thu, 07 Jan 2010 10:43:25 -0800
Message-ID: <4B462B4D.5020203@ca.com> (raw)
On 01/05/2010 08:34 PM, Robert Haas [robertmhaas@gmail.com] wrote:
> - If you have other queries where this index helps (even though it is
> hurting this one), then you're going to have to find a way to execute
> the query without using bound parameters - i.e. with the actual values
> in there instead of $1 through $4. That will allow the planner to see
> that the index scan is a loser because it will see that there are a
> lot of rows in the specified range of ts_interval_start_times.
I think that this is possible without too much work.
FYI - this test is still running and the same query has been executed at
least 2 more times (it gets done 1-24 times per day) since it took 124M
ms with acceptable response times (several secs). I don't see how either
of the 2 query plans posted could've taken that long (and the actually
execution times I posted confirm this), so I'm assuming that some other
plan was used for the 124M ms execution. Seems like it must have been
some NxM plan. Do you think that autovacuuming more frequently would
prevent the query planner from making this poor choice?
Brian
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: brian.cox@ca.com, robertmhaas@gmail.com
Subject: Re: query looping?
In-Reply-To: <4B462B4D.5020203@ca.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