CREATE INDEX speeds up query on 31 row table ... - Mailing list pgsql-hackers

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,
showns                            WHERE b.start_date <= now()                              AND ( b.end_date >= now()
ORb.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.27rows=1 width=8) (actual time=2.808..2.814 rows=1 loops=1)            ->  Merge Join
(cost=2.15..123.20rows=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.78rows=32 width=12) (actual time=0.024..1.024 rows=32 loops=1)                  ->  Sort
(cost=2.15..2.22rows=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=26loops=1)                              Filter: ((end_date >= now()) OR (end_date IS NULL))    ->  Nested Loop
(cost=0.00..63.87rows=2 width=50) (actual time=3.615..3.615 rows=1 loops=1)          ->  Seq Scan on banner b
(cost=0.00..1.64rows=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.21rows=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)
(actualtime=0.007..160.087 rows=32 loops=1)                  ->  Hash  (cost=1.48..1.48 rows=28 width=4) (actual
time=0.466..0.466rows=0 loops=1)                        ->  Seq Scan on banner b  (cost=0.00..1.48 rows=28 width=4)
(actualtime=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)
JoinFilter: ("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.74rows=10 width=50) (actual time=0.237..0.769 rows=23 loops=1)                ->  Seq Scan on banner b
(cost=0.00..1.64rows=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@hub.org           Yahoo!: yscrappy              ICQ: 7615664