From: Tom Lane <tgl@sss.pgh.pa.us>
To: Mark Stosberg <mark@summersault.com>
Cc: Merlin Moncure <mmoncure@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: cube operations slower than geo_distance() on production server
Date: Wed, 14 Feb 2007 02:15:44 -0500
Message-ID: <18714.1171437344@sss.pgh.pa.us> (raw)
In-Reply-To: <20070213204747.GD23491@summersault.com>
References: <eqiht3$2fvu$1@news.hub.org>
<b42b73150702091731x57996613hb50af0296007436c@mail.gmail.com>
<eqq3k3$1mpu$1@news.hub.org>
<b42b73150702121103l297b690w84dde8ef9ef8d2f1@mail.gmail.com>
<eqqgc5$kle$1@news.hub.org>
<b42b73150702130615t22776654u9e0925e0fe368bc1@mail.gmail.com>
<b42b73150702130631x332e4f18s34a057c0c47c5e8c@mail.gmail.com>
<20070213204747.GD23491@summersault.com>
Mark Stosberg <mark@summersault.com> writes:
> For reference, here's two versions of the query. The first uses
> the old geo_distance(), and the second one is the new cube query I'm
> trying, inspired by your suggested refactoring.
You didn't show EXPLAIN ANALYZE output :-(
Looking back in the thread, the last E.A. output I see is in your
message of 2/12 11:11, and the striking thing there is that it seems all
the time is going into one indexscan:
-> Bitmap Index Scan on pets_pet_state_idx (cost=0.00..562.50 rows=39571 width=0) (actual time=213.620..213.620 rows=195599 loops=82)
Index Cond: ((pet_state)::text = 'available'::text)
Total runtime: 17933.675 ms
213.620 * 82 = 17516.840, so this step is all but 400msec of the run.
There are two things wrong here: first, that the estimated row count is
only 20% of actual; it should certainly not be that far off for such a
simple condition. I wonder if your vacuum/analyze procedures are
actually working. Second, you mentioned somewhere along the line that
'available' pets are about 10% of all the entries, which means that this
indexscan is more than likely entirely counterproductive: it would be
cheaper to ignore this index altogether.
Suggestions:
1. ANALYZE the table by hand, try the explain again and see if this
rowcount estimate gets better. If so, you need to look into why your
existing procedures aren't keeping the stats up to date.
2. If, with a more accurate rowcount estimate, the planner still wants
to use this index, try discouraging it. Brute force would be to drop
the index. If there are less-common pet_states that are actually worth
searching for, maybe keep the index but change it to a partial index
WHERE pet_state != 'available'.
Also, I don't see that you mentioned anywhere what PG version you are
running, but if it's not the latest then an update might help. I recall
having fixed a bug that made the planner too eager to AND on an index
that wouldn't actually help much ... which seems to fit this problem
description pretty well.
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, mark@summersault.com, mmoncure@gmail.com
Subject: Re: cube operations slower than geo_distance() on production server
In-Reply-To: <18714.1171437344@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