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

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=#