Re: Question about query optimization - Mailing list pgsql-general

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

On 11/15/06, Matthias.Pitzl@izb.de <Matthias.Pitzl@izb.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[.singh]@EnterpriseDB.com
singh.gurjeet@{ gmail | hotmail | yahoo }.com