Re: Decrease in performance with 7.3/optimizing a query

On Sun, 22 Dec 2002 13:59:26 -0800, Harry <h3(at)x-maru(dot)org> wrote:
>=> select set,max(contig) from cap3 where contig!=0 group by set order by set;

>"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.

This seems to call for normalization:

CREATE TABLE set (
id int NOT NULL PRIMARY KEY,
txt text NOT NULL
);
CREATE TABLE cap3 (
seqid INT,
setid INT REFERENCES set,
contig INT,
CONSTRAINT cap3_sc_uq UNIQUE (setid, contig)
);

Postgres automatically creates the indices you need. Given the low
number of set entries, you would write your query like

SELECT txt, (SELECT contig
FROM cap3
WHERE cap3.setid = set.id AND contig != 0
ORDER BY setid desc, contig desc
LIMIT 1) AS maxcontig
FROM set
ORDER BY txt;

which should perform like

| Sort (cost=1.34..1.37 rows=12 width=32)
| (actual time=859.27..859.33 rows=12 loops=1)
| Sort Key: txt
| -> Seq Scan on "set" (cost=0.00..1.12 rows=12 width=32) (actual time=72.80..857.80 rows=12 loops=1)
| SubPlan
| -> Limit (cost=0.00..0.15 rows=1 width=8) (actual time=71.11..71.24 rows=1 loops=12)
| -> Index Scan Backward using cap3_sc_uq on cap3 (cost=0.00..2470.74 rows=16383 width=8) (actual time=70.99..71.10 rows=2 loops=12)
| Index Cond: (setid = $0)
| Filter: (contig <> 0)
| Total runtime: 860.82 msec

... on a K5, 105 MHz, 48 MB :-)

BTW, this is one of the rare cases where I recommed using a subselect
instead of a join.

Servus
Manfred