CREATE INDEX speeds up query on 31 row table ...

Josh asked me to post this, since it was just "odd" ... I have
pg_autovacuum running on the table, with output looking for it looking
like:

[2004-09-30 02:29:47 PM] Performing: VACUUM ANALYZE "public"."shown"
[2004-09-30 02:35:11 PM] Performing: ANALYZE "public"."shown"
[2004-09-30 02:40:22 PM] Performing: VACUUM ANALYZE "public"."shown"
[2004-09-30 02:45:54 PM] Performing: ANALYZE "public"."shown"
[2004-09-30 02:51:03 PM] Performing: VACUUM ANALYZE "public"."shown"
[2004-09-30 02:56:29 PM] Performing: ANALYZE "public"."shown"
[2004-09-30 03:01:44 PM] Performing: VACUUM ANALYZE "public"."shown"

Its a *very* busy table ... and running on a 7.4.0 database ...

With:

explain analyze SELECT b.banner_id, b.filename, b.option_lvl, b.redirect_url
FROM banner b, shown s
WHERE b.start_date <= now()
AND ( b.end_date >= now() OR b.end_date IS NULL )
AND b.banner_id = s.banner_id
AND s.counter = ( SELECT min(counter)
FROM shown s, banner b
WHERE b.banner_id = s.banner_id
AND ( b.end_date >= now() OR b.end_date IS NULL ) )
LIMIT 1;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=123.27..155.21 rows=1 width=50) (actual time=3.630..3.635 rows=1 loops=1)
InitPlan
-> Aggregate (cost=123.27..123.27 rows=1 width=8) (actual time=2.808..2.814 rows=1 loops=1)
-> Merge Join (cost=2.15..123.20 rows=28 width=8) (actual time=0.615..2.528 rows=26 loops=1)
Merge Cond: ("outer".banner_id = "inner".banner_id)
-> Index Scan using banner_id_shown on shown s (cost=0.00..137.78 rows=32 width=12) (actual time=0.024..1.024 rows=32 loops=1)
-> Sort (cost=2.15..2.22 rows=28 width=4) (actual time=0.554..0.833 rows=26 loops=1)
Sort Key: b.banner_id
-> Seq Scan on banner b (cost=0.00..1.48 rows=28 width=4) (actual time=0.041..0.280 rows=26 loops=1)
Filter: ((end_date >= now()) OR (end_date IS NULL))
-> Nested Loop (cost=0.00..63.87 rows=2 width=50) (actual time=3.615..3.615 rows=1 loops=1)
-> Seq Scan on banner b (cost=0.00..1.64 rows=10 width=50) (actual time=0.042..0.042 rows=1 loops=1)
Filter: ((start_date <= now()) AND ((end_date >= now()) OR (end_date IS NULL)))
-> Index Scan using banner_id_shown on shown s (cost=0.00..6.21 rows=1 width=4) (actual time=3.537..3.537 rows=1 loops=1)
Index Cond: ("outer".banner_id = s.banner_id)
Filter: (counter = $0)
Total runtime: 3.929 ms
(17 rows)

Without:

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------
Limit (cost=2693.02..4038.17 rows=1 width=50) (actual time=190.296..190.302 rows=1 loops=1)
InitPlan
-> Aggregate (cost=2691.38..2691.38 rows=1 width=8) (actual time=161.848..161.853 rows=1 loops=1)
-> Hash Join (cost=1.55..2691.31 rows=28 width=8) (actual time=1.299..161.558 rows=26 loops=1)
Hash Cond: ("outer".banner_id = "inner".banner_id)
-> Seq Scan on shown s (cost=0.00..2689.32 rows=32 width=12) (actual time=0.007..160.087 rows=32 loops=1)
-> Hash (cost=1.48..1.48 rows=28 width=4) (actual time=0.466..0.466 rows=0 loops=1)
-> Seq Scan on banner b (cost=0.00..1.48 rows=28 width=4) (actual time=0.062..0.276 rows=26 loops=1)
Filter: ((end_date >= now()) OR (end_date IS NULL))
-> Nested Loop (cost=1.64..2691.94 rows=2 width=50) (actual time=190.281..190.281 rows=1 loops=1)
Join Filter: ("inner".banner_id = "outer".banner_id)
-> Seq Scan on shown s (cost=0.00..2689.40 rows=4 width=4) (actual time=189.326..189.326 rows=1 loops=1)
Filter: (counter = $0)
-> Materialize (cost=1.64..1.74 rows=10 width=50) (actual time=0.237..0.769 rows=23 loops=1)
-> Seq Scan on banner b (cost=0.00..1.64 rows=10 width=50) (actual time=0.131..0.394 rows=23 loops=1)
Filter: ((start_date <= now()) AND ((end_date >= now()) OR (end_date IS NULL)))
Total runtime: 190.510 ms
(17 rows)

banners=# select count(*) from shown;
count
-------
32
(1 row)

----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: scrappy(at)hub(dot)org Yahoo!: yscrappy ICQ: 7615664