Re: Question about query optimization

Hello Gurjeet!

Tried your suggestion but this is just a marginal improvement.
Our query needs 126 ms time, your query 110 ms.

Greetings,
Matthias

-----Original Message-----
From: pgsql-general-owner(at)postgresql(dot)org
[mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Gurjeet Singh
Sent: Wednesday, November 15, 2006 4:18 PM
To: Matthias(dot)Pitzl(at)izb(dot)de
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] Question about query optimization

On 11/15/06, Matthias(dot)Pitzl(at)izb(dot)de <mailto:Matthias(dot)Pitzl(at)izb(dot)de>
<Matthias(dot)Pitzl(at)izb(dot)de <mailto:Matthias(dot)Pitzl(at)izb(dot)de> > wrote:

Is there any other, and more performat way, to get the last history entry
for a given date than this query?

Create an (independent) index on history_timestamp column and use a min/max
in the subquery.

More specifically, your query should look like this:
SELECT *
FROM component
JOIN component_history AS c_h
USING(component_id)
WHERE history_timestamp = (SELECT max(history_timestamp)
FROM component_history
WHERE c_h.component_id =
component_history.component_id
)

Here's a session snippet for an example of how drastically that can reduce
the cost and the run-time:

postgres=# drop table t;
DROP TABLE

postgres=# create table t ( a int, b int );
CREATE TABLE

postgres=# insert into t select s, 99999-s from generate_series(0,99999) as
s;
INSERT 0 100000

postgres=# analyze t;
ANALYZE

postgres=# explain select count(*) from t o where a = (select max(a) from t
i wh
ere i.b = o.b );
QUERY PLAN
--------------------------------------------------------------------------
Aggregate (cost=179103292.25..179103292.26 rows=1 width=0)
-> Seq Scan on t o (cost=0.00..179103291.00 rows=500 width=0)
Filter: (a = (subplan))
SubPlan
-> Aggregate (cost= 1791.01..1791.02 rows=1 width=4)
-> Seq Scan on t i (cost=0.00..1791.00 rows=1 width=4)
Filter: (b = $0)
(7 rows)
Time: 0.000 ms

postgres=# create index ind_t_a on t(a) ;
CREATE INDEX
Time: 719.000 ms

postgres=# create index ind_t_b on t(b);
CREATE INDEX
Time: 750.000 ms

postgres=# explain select count(*) from t o where a = (select max(a) from t
i wh
ere i.b = o.b );
QUERY PLAN

----------------------------------------------------------------------------
----
-------
Aggregate (cost=806146.25..806146.26 rows=1 width=0)
-> Seq Scan on t o (cost= 0.00..806145.00 rows=500 width=0)
Filter: (a = (subplan))
SubPlan
-> Aggregate (cost=8.03..8.04 rows=1 width=4)
-> Index Scan using ind_t_b on t i (cost= 0.00..8.03
rows=1 wi
dth=4)
Index Cond: (b = $0)
(7 rows)
Time: 15.000 ms

/* and now the execution times */

postgres=# drop index ind_t_a, ind_t_b;
DROP INDEX
Time: 0.000 ms

postgres=# select count(*) from t o where a = (select max(a) from t i where
i.b
= o.b );
Cancel request sent (had to cancel after 1 minute)
ERROR: canceling statement due to user request

postgres=# create index ind_t_a on t(a) ;
CREATE INDEX
Time: 687.000 ms

postgres=# create index ind_t_b on t(b);
CREATE INDEX
Time: 765.000 ms

postgres=# select count(*) from t o where a = (select max(a) from t i where
i.b
= o.b );
count
--------
100000
(1 row)
Time: 2704.000 ms

postgres=#

--
gurjeet[(dot)singh](at)EnterpriseDB(dot)com
singh(dot)gurjeet(at){ gmail | hotmail | yahoo }.com