Skip site navigation (1) Skip section navigation (2)

Re: Decrease in performance with 7.3/optimizing a query

From: Manfred Koizar <mkoi-pg(at)aon(dot)at>
To: Harry <h3(at)x-maru(dot)org>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: Decrease in performance with 7.3/optimizing a query
Date: 2002-12-23 10:46:32
Message-ID: bqld0v878s6a6rbvq4cv4no8f3d8cad8tb@4ax.com (view raw or flat)
Thread:
Lists: pgsql-novice
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

In response to

pgsql-novice by date

Next:From: Chris BogetDate: 2002-12-23 13:25:03
Subject: Simple query question
Previous:From: HarryDate: 2002-12-23 08:01:32
Subject: Re: Decrease in performance with 7.3/optimizing a query

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group