I just saw that someone created a view on one of my DBs (statistics DB) to get the top IP that did requests to a webserver (over the last 10 minutes). The view looks like this:
QUOTE:
SELECT xxxxxx.remote_address AS "Remote Host", count(1) AS "Hits"
FROM apache.xxxxxx
WHERE xxxxxx.logdate > (now() - '00:10:00'::interval)
GROUP BY xxxxxx.remote_address
ORDER BY count(1) DESC;
No problem with it (at least not with the query). But I noticed that the load was unexpectedly when the view was executed. So I had a look that the query plan for this view:
QUOTE:
Sort (cost=46678.83..46678.84 rows=1 width=17) (actual time=32655.224..32655.227 rows=1 loops=1)
Sort Key: count(1)
-> HashAggregate (cost=46678.81..46678.82 rows=1 width=17) (actual time=32655.065..32655.070 rows=1 loops=1)
-> Seq Scan on xxxxxxx (cost=0.00..46678.49 rows=64 width=17) (actual time=32654.736..32654.877 rows=6 loops=1)
Filter: (logdate > (now() - '00:10:00'::interval))
Total runtime: 32655.806 ms
Full table-scan? WTF?! What an idiot!!!11XI!
So I quickly created an index on the logdate for that table and what's the result? Correct....:
QUOTE:
Sort (cost=7.21..7.22 rows=1 width=17) (actual time=13.703..13.706 rows=1 loops=1)
Sort Key: count(1)
-> HashAggregate (cost=7.19..7.20 rows=1 width=17) (actual time=13.293..13.296 rows=1 loops=1)
-> Index Scan using xxxxxxx_idx_timestamp on xxxxxxxx (cost=0.00..6.87 rows=64 width=17) (actual time=13.027..13.145 rows=6 loops=1)
Index Cond: (logdate > (now() - '00:10:00'::interval))
Total runtime: 14.206 ms
(6 rows)
