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

On Thu, 30 Sep 2004, Tom Lane wrote:

> Greg Stark <gsstark(at)mit(dot)edu> writes:
>> You say it's "*very* busy" is it possible there are hundreds or thousands of
>> tuples in there that are uncommitted or committed after this query starts?
>
> More specifically, I bet there's a huge number of completely empty
> pages, which would be read by a seqscan but not an indexscan. VACUUM
> FULL should fix it nicely, but it's odd that autovacuum isn't keeping
> a lid on the file size. Maybe with so few live rows, it's confused into
> thinking it doesn't need to vacuum the table often?

in the last hour or so:

[2004-09-30 03:41:18 PM] Performing: ANALYZE "public"."shown"
[2004-09-30 03:59:50 PM] Performing: VACUUM ANALYZE "public"."shown"
[2004-09-30 04:10:27 PM] Performing: VACUUM ANALYZE "public"."shown"
[2004-09-30 04:21:36 PM] Performing: ANALYZE "public"."shown"
[2004-09-30 04:27:05 PM] Performing: VACUUM ANALYZE "public"."shown"
[2004-09-30 04:32:17 PM] Performing: ANALYZE "public"."shown"
[2004-09-30 04:37:50 PM] Performing: VACUUM ANALYZE "public"."shown"
[2004-09-30 04:49:05 PM] Performing: VACUUM ANALYZE "public"."shown"

database directory itself is:

db# du 17144
28696 17144

after a vacuum full:

du 17144
6530 17144

Based on the following, I don't think that I need to raise my fsm settings
enough to make much of a difference ... or am I reading it wrong?

INFO: free space map: 674 relations, 9298 pages stored; 21360 total pages needed
DETAIL: Allocated FSM size: 1000 relations + 20000 pages = 178 kB shared memory.

Running with the index now results in:

Limit (cost=4.85..6.57 rows=1 width=50) (actual time=3.370..3.376 rows=1
loops=1)
InitPlan
-> Aggregate (cost=3.43..3.43 rows=1 width=8) (actual
time=2.562..2.567 rows=1 loops=1)
-> Hash Join (cost=1.57..3.35 rows=29 width=8) (actual
time=1.739..2.338 rows=27 loops=1)
Hash Cond: ("outer".banner_id = "inner".banner_id)
-> Seq Scan on shown s (cost=0.00..1.33 rows=33
width=12) (actual time=0.011..0.309 rows=33 loops=1)
-> Hash (cost=1.50..1.50 rows=29 width=4) (actual
time=0.589..0.589 rows=0 loops=1)
-> Seq Scan on banner b (cost=0.00..1.50 rows=29
width=4) (actual time=0.122..0.399 rows=27 loops=1)
Filter: ((end_date >= now()) OR (end_date IS
NULL))
-> Hash Join (cost=1.42..3.14 rows=1 width=50) (actual
time=3.355..3.355 rows=1 loops=1)
Hash Cond: ("outer".banner_id = "inner".banner_id)
-> Seq Scan on banner b (cost=0.00..1.66 rows=10 width=50)
(actual time=0.030..0.151 rows=16 loops=1)
Filter: ((start_date <= now()) AND ((end_date >= now()) OR
(end_date IS NULL)))
-> Hash (cost=1.41..1.41 rows=3 width=4) (actual
time=2.800..2.800 rows=0 loops=1)
-> Seq Scan on shown s (cost=0.00..1.41 rows=3 width=4)
(actual time=2.684..2.735 rows=6 loops=1)
Filter: (counter = $0)
Total runtime: 3.913 ms
(17 rows)

and without:

Limit (cost=4.85..6.57 rows=1 width=50) (actual time=2.111..2.116 rows=1
loops=1)
InitPlan
-> Aggregate (cost=3.43..3.43 rows=1 width=8) (actual
time=1.430..1.435 rows=1 loops=1)
-> Hash Join (cost=1.57..3.35 rows=29 width=8) (actual
time=0.676..1.236 rows=27 loops=1)
Hash Cond: ("outer".banner_id = "inner".banner_id)
-> Seq Scan on shown s (cost=0.00..1.33 rows=33
width=12) (actual time=0.007..0.290 rows=33 loops=1)
-> Hash (cost=1.50..1.50 rows=29 width=4) (actual
time=0.422..0.422 rows=0 loops=1)
-> Seq Scan on banner b (cost=0.00..1.50 rows=29
width=4) (actual time=0.025..0.246 rows=27 loops=1)
Filter: ((end_date >= now()) OR (end_date IS
NULL))
-> Hash Join (cost=1.42..3.14 rows=1 width=50) (actual
time=2.098..2.098 rows=1 loops=1)
Hash Cond: ("outer".banner_id = "inner".banner_id)
-> Seq Scan on banner b (cost=0.00..1.66 rows=10 width=50)
(actual time=0.024..0.225 rows=25 loops=1)
Filter: ((start_date <= now()) AND ((end_date >= now()) OR
(end_date IS NULL)))
-> Hash (cost=1.41..1.41 rows=3 width=4) (actual
time=1.562..1.562 rows=0 loops=1)
-> Seq Scan on shown s (cost=0.00..1.41 rows=3 width=4)
(actual time=1.517..1.537 rows=1 loops=1)
Filter: (counter = $0)
Total runtime: 2.393 ms
(17 rows)

so now we're in the same ball park, at least ...

I'll keep an eye on things to see if pg_autovacuum can 'keep up' without
having to re-add the index ...

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