From: Tom Lane <tgl@sss.pgh.pa.us>
To: Karl Wright <kwright@metacarta.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Performance query about large tables, lots of concurrent access
Date: Tue, 19 Jun 2007 10:36:34 -0400
Message-ID: <27690.1182263794@sss.pgh.pa.us> (raw)
In-Reply-To: <4677B831.8060704@metacarta.com>
References: <4676DD57.7070304@metacarta.com>
<14291.1182221811@sss.pgh.pa.us>
<4677B831.8060704@metacarta.com>
Karl Wright <kwright@metacarta.com> writes:
> Also, as I said before, I have done extensive query analysis and found
> that the plans for the queries that are taking a long time are in fact
> very reasonable. Here's an example from the application log of a query
> that took way more time than its plan would seem to indicate it should:
> [2007-06-18 09:39:49,797]ERROR Plan: Index Scan using i1181764142395 on
> intrinsiclink (cost=0.00..14177.29 rows=5 width=253)
> [2007-06-18 09:39:49,797]ERROR Plan: Index Cond: ((jobid = $2) AND
> ((childidhash)::text = ($3)::text))
> [2007-06-18 09:39:49,797]ERROR Plan: Filter: ((childid = ($4)::text)
> AND ((isnew = ($5)::bpchar) OR (isnew = ($6)::bpchar)))
I see the discussion thread has moved on to consider lack-of-vacuuming
as the main problem, but I didn't want to let this pass without
comment. The above plan is not necessarily good at all --- it depends
on how many rows are selected by the index condition alone (ie, jobid
and childidhash) versus how many are selected by the index and filter
conditions. If the index retrieves many rows, most of which are
eliminated by the filter condition, it's still gonna take a long time.
In this case it looks like the planner is afraid that that's exactly
what will happen --- a cost of 14177 suggests that several thousand row
fetches are expected to happen, and yet it's only predicting 5 rows out
after the filter. It's using this plan anyway because it has no better
alternative, but you should think about whether a different index
definition would help.
regards, tom lane
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: tgl@sss.pgh.pa.us, kwright@metacarta.com
Subject: Re: Performance query about large tables, lots of concurrent access
In-Reply-To: <27690.1182263794@sss.pgh.pa.us>
* 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