On Thu, 30 Sep 2004, Tom Lane wrote:
Greg Stark <[EMAIL PROTECTED]> 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: [EMAIL PROTECTED] Yahoo!: yscrappy ICQ: 7615664
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
