I have a somewhat large table (more than 100 million rows) that contains log data with start_time and end_time columns. When I try to do queries on this table I always find them slower than what I need and what I believe should be possible.
For example, I limited the following query to just a single day and it still is much slower than what I would expect. In reality I need to do queries that span a few weeks.
explain analyze select * from ad_log where date(start_time) < date('2009-03-31') and date(start_time) >= date('2009-03-30');
Bitmap Heap Scan on ad_log (cost=73372.57..3699152.24 rows=2488252 width=32) (actual time=49792.862..64611.255 rows=2268490 loops=1)
Recheck Cond: ((date(start_time) < '2009-03-31'::date) AND (date(start_time) >= '2009-03-30'::date))
-> Bitmap Index Scan on ad_log_date_all (cost=0.00..72750.51 rows=2488252 width=0) (actual time=49776.332..49776.332 rows=2268490 loops=1)
Index Cond: ((date(start_time) < '2009-03-31'::date) AND (date(start_time) >= '2009-03-30'::date))
Total runtime: 65279.352 ms
The definition of the table is:
Column | Type | Modifiers
------------+-----------------------------+------------------------------------------------------------
ad_log_id | integer | not null default nextval('ad_log_ad_log_id_seq'::regclass)
channel | integer | not null
player | integer | not null
ad | integer | not null
start_time | timestamp without time zone |
end_time | timestamp without time zone |
Indexes:
"ad_log_pkey" PRIMARY KEY, btree (ad_log_id)
"ad_log_unique" UNIQUE, btree (channel, player, ad, start_time, end_time)
"ad_log_ad" btree (ad)
"ad_log_ad_date" btree (ad, date(start_time))
"ad_log_channel" btree (channel)
"ad_log_channel_date" btree (channel, date(start_time))
"ad_log_date_all" btree (date(start_time), channel, player, ad)
"ad_log_player" btree (player)
"ad_log_player_date" btree (player, date(start_time))
Foreign-key constraints:
"ad_log_ad_fkey" FOREIGN KEY (ad) REFERENCES ads(id)
"ad_log_channel_fkey" FOREIGN KEY (channel) REFERENCES channels(id)
"ad_log_player_fkey" FOREIGN KEY (player) REFERENCES players_history(id)
Triggers:
rollup_ad_logs_daily AFTER INSERT ON ad_log FOR EACH ROW EXECUTE PROCEDURE rollup_ad_logs_daily()
Any suggestions would be appreciated.
--Rainer
