Re: Decrease in performance with 7.3/optimizing a query

On Sun, 22 Dec 2002 13:59:26 -0800, Harry <h3@x-maru.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.10rows=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