On Wednesday 08 June 2011 19:47, tv@xxxxxxxx wrote:
> Have you tried to create a composite index on those two columns? Not sure
> if that helps but I'd try that.
>
> Tomas
This finally works well enough
CREATE TABLE tdiag (
diag_id integer DEFAULT nextval('diag_id_seq'::text),
create_time timestamp with time zone default now(),
....
PRIMARY KEY (diag_id)
);
-- ************ COMPOSITE INDEX
create index tdiag_id_create on tdiag(diag_id, create_time);
alter table tdiag alter column diag_id set statistics 1000;
alter table tdiag alter column create_time set statistics 1000;
and then just do the original query
symstream2=> explain analyze select * from tdiag where
symstream2-> (create_time >= '2011-06-07 02:00:00.000000+0' and create_time
< '2011-06-10 07:58:03.000000+0') and diag_level <= 1
symstream2-> order by diag_id LIMIT 100 OFFSET 800;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=6064.19..6822.21 rows=100 width=112) (actual
time=1496.644..1497.094 rows=100 loops=1)
-> Index Scan using tdiag_id_create on tdiag (cost=0.00..1320219.58
rows=174166 width=112) (actual time=1409.285..1495.831 rows=900 loops=1)
Index Cond: ((create_time >= '2011-06-07 12:00:00+10'::timestamp with
time zone) AND (create_time < '2011-06-10 17:58:03+10'::timestamp with time
zone))
Filter: (diag_level <= 1)
Total runtime: 1497.297 ms
If I had set the primary key to (diag_id, create_time) would simple queries on
diag_id still work well i.e.
select * from tdiag where diag_id = 1234;
--
Anthony Shipman | -module(erlang).
Anthony.Shipman@xxxxxxxxxxxxx | ''(_)->0. %-)
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
