Thanks for all of the suggestions so far. I've been trying to reduce the
number of indices I have, but I'm running into a problem. I have a need to
do queries on this table with criteria applied to the date and possibly any
or all of the other key columns. As a reminder, here's my table:
Table "public.ad_log"
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 |
So, I need indices that make it fast querying against start_time as well as
all possible combinations of channel, player, and ad. Below is a sample
query that uses all of these (note that I've removed actual strings to
protect customer data). The result is fine in terms of speed, but since it's
using the ad_log_ad_date index I'm wondering what the best strategy is to
cover queries that don't specify an ad. Should I have 4 indices, one with
just the start_time (for when no other columns are specified) and the other
three each with the start_time and the three other criteria: channel,
player, and ad? I'm currently experimenting with various options, but since
it takes a couple of hours to create a particular index this is taking a
while.
# explain analyze SELECT ad_log.ad_log_id, channels.name as channel_name,
players.name as player_name, ads.name as ad_name, start_time, end_time,
(data IS NOT NULL) AS has_screenshot FROM channels, players,
players_history, ads, ad_log LEFT OUTER JOIN ad_log_screenshot USING
(ad_log_id) WHERE channel=channels.id AND player=players_history.id AND
players_history.player_instance = players.id AND ad=ads.id AND channels.name
LIKE '<some channel>' AND players.name LIKE '<some player>' AND ads.name
LIKE '<some ad>' AND date(start_time) BETWEEN '2009-01-20' AND
date('2009-01-21') ORDER BY channels.name, players.name, start_time,
ads.name LIMIT 100 OFFSET 100;
QUERY PLAN
----------------------------------------------------------------------------
----------------------------------------------------------------------------
--------------------------
Limit (cost=7425.26..7425.26 rows=1 width=120) (actual
time=1256.116..1256.202 rows=39 loops=1)
-> Sort (cost=7425.26..7425.26 rows=1 width=120) (actual
time=1255.965..1256.068 rows=139 loops=1)
Sort Key: channels.name, players.name, ad_log.start_time, ads.name
Sort Method: quicksort Memory: 35kB
-> Nested Loop Left Join (cost=0.01..7425.25 rows=1 width=120)
(actual time=179.086..1255.451 rows=139 loops=1)
-> Nested Loop (cost=0.01..7417.06 rows=1 width=88) (actual
time=137.488..1212.531 rows=139 loops=1)
Join Filter: (ad_log.channel = channels.id)
-> Nested Loop (cost=0.01..7415.73 rows=1 width=60)
(actual time=120.308..1192.867 rows=139 loops=1)
Join Filter: (players_history.id = ad_log.player)
-> Nested Loop (cost=0.00..36.92 rows=1
width=17) (actual time=21.960..23.405 rows=1 loops=1)
Join Filter: (players.id =
players_history.player_instance)
-> Seq Scan on players (cost=0.00..11.80
rows=1 width=17) (actual time=5.981..6.083 rows=1 loops=1)
Filter: (name ~~ '<some
player>'::text)
-> Seq Scan on players_history
(cost=0.00..14.50 rows=850 width=8) (actual time=15.880..16.592 rows=850
loops=1)
-> Nested Loop (cost=0.01..7371.03 rows=622
width=51) (actual time=75.161..1156.076 rows=15600 loops=1)
-> Seq Scan on ads (cost=0.00..72.79
rows=1 width=27) (actual time=15.776..31.975 rows=1 loops=1)
Filter: (name ~~ '<some ad>'::text)
-> Index Scan using ad_log_ad_date on
ad_log (cost=0.01..7267.77 rows=2438 width=32) (actual
time=59.375..1095.229 rows=15600 loops=1)
Index Cond: ((ad_log.ad = ads.id) AND
(date(ad_log.start_time) >= '2009-01-20'::date) AND (date(ad_log.start_time)
<= '2009-01-21'::date))
-> Seq Scan on channels (cost=0.00..1.31 rows=1
width=36) (actual time=0.128..0.132 rows=1 loops=139)
Filter: (channels.name ~~ '<some channel>'::text)
-> Index Scan using ad_log_screenshot_pkey on
ad_log_screenshot (cost=0.00..8.18 rows=1 width=36) (actual
time=0.304..0.304 rows=0 loops=139)
Index Cond: (ad_log.ad_log_id =
ad_log_screenshot.ad_log_id)
Total runtime: 1256.572 ms
Thanks,
--Rainer
