Decrease in performance with 7.3/optimizing a query

From: Harry <h3(at)x-maru(dot)org>
To: pgsql-novice(at)postgresql(dot)org
Subject: Decrease in performance with 7.3/optimizing a query
Date: 2002-12-22 21:59:26
Message-ID: 20021222135926.2d9c2868.h3@x-maru.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

Hello,

I have a fairly simple table of about 250,000 rows as so:

Column | Type | Modifiers
--------+---------+-----------
seqid | integer |
set | text |
contig | integer |
Indexes: cap3_set_key btree ("set")

I have occasion to perform the following query on it:

=> select set,max(contig) from cap3 where contig!=0 group by set order by set;

Previously, with Postgres 7.2.1, this query would take about 10 seconds - not
exactly speedy, but tolerable. However, since upgrading to 7.3, this query now
takes about 30 seconds.

I've looked through the release notes, tweaked postgresql.conf, and played
around with creating various indices but nothing I've done has made a
difference.

While it would be interesting to know why the decrease going from 7.2.1 to 7.3,
I'm more concerned about just getting this query to run in a more reasonable
time. Does anyone have any suggestions?

FWIW: "seqid" is a foreign key to another table of about 25,000 rows, "set" is
a string of about 20-30 characters of which there are currently about a dozen
distinct ones, and "contig" is a sequence of up to a few thousand. Rows are
unique on set,contig. The "explain" of the query looks like this:

QUERY PLAN
-------------------------------------------------------------------------------
Aggregate (cost=22656.46..23897.71 rows=16550 width=29)
-> Group (cost=22656.46..23483.96 rows=165501 width=29)
-> Sort (cost=22656.46..23070.21 rows=165501 width=29)
Sort Key: "set"
-> Seq Scan on cap3 (cost=0.00..4650.46 rows=165501 width=29)
Filter: (contig <> 0)

The aforementioned upgrade was actually part of a larger upgrade of the entire
system (RH 6.2 -> RH 7.3), so the performance decrease may be the consequence
of something outside of Postgres (I'm hoping this isn't the case).

Hardware: 650 MHz PIII, 1 GB RAM

Thanks,
Harry

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2002-12-23 02:31:30 Re: Decrease in performance with 7.3/optimizing a query
Previous Message brew 2002-12-22 17:52:36 passing variables