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-----On 11/15/06, Matthias.Pitzl@xxxxxx <Matthias.Pitzl@xxxxxx> wrote:
From: pgsql-general-owner@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] On Behalf Of Gurjeet Singh
Sent: Wednesday, November 15, 2006 4:18 PM
To: Matthias.Pitzl@xxxxxx
Cc: pgsql-general@xxxxxxxxxxxxxx
Subject: Re: [GENERAL] Question about query optimizationIs 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[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com
