From: Tom Lane <tgl@sss.pgh.pa.us>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: select slows from 3 seconds to 30 seconds
Date: Fri, 04 May 2007 20:57:07 -0400
Message-ID: <12822.1178326627@sss.pgh.pa.us> (raw)
In-Reply-To: <200705041649.54193.gary.stainburn@ringways.co.uk>
References: <200705041649.54193.gary.stainburn@ringways.co.uk>
Gary Stainburn <gary.stainburn@ringways.co.uk> writes:
> I have the query below which when run takes approx 3 seconds. However when I
> add the condition 'and w_ws_id = 10' onto the end changes to 30+ seconds.
> Can anyone see why? I've included the explain for the long select.
You really ought to provide EXPLAIN ANALYZE output for both versions if
you want intelligent commentary --- otherwise we're just guessing.
But I would guess the problem is that the planner is way off about the
number of rows satisfying the joint condition --- it thinks two:
> -> Index Scan using walon_hide_index on walon (cost=0.00..85.04 rows=2 width=140)
> Index Cond: (w_hide = 0)
> Filter: (w_ws_id = 10)
This plan is good if there really are only a couple rows, but degrades
quickly if there are many...
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-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, gary.stainburn@ringways.co.uk
Subject: Re: select slows from 3 seconds to 30 seconds
In-Reply-To: <12822.1178326627@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