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 |