Decrease in performance with 7.3/optimizing a query
От | Harry |
---|---|
Тема | Decrease in performance with 7.3/optimizing a query |
Дата | |
Msg-id | 20021222135926.2d9c2868.h3@x-maru.org обсуждение исходный текст |
Ответы |
Re: Decrease in performance with 7.3/optimizing a query
Re: Decrease in performance with 7.3/optimizing a query |
Список | 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
В списке pgsql-novice по дате отправления: