On 07/ago/08, at 20:37, Giorgio Valoti wrote:
[…]
If you haven't mucked with the cost parameters, the only way I can
think
of to get this result is to have an enormously bloated table that's
mostly empty. Maybe you need to review your vacuuming procedures.
I’ll review them.
I’ve manually vacuum’ed the table:
logs=> VACUUM FULL verbose analyze blackbox;
INFO: vacuuming "public.blackbox"
INFO: "blackbox": found 0 removable, 247736 nonremovable row versions
in 8436 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 137 to 1210 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 894432 bytes.
0 pages are or will become empty, including 0 at the end of the table.
2926 pages containing 564212 free bytes are potential move destinations.
CPU 0.00s/0.04u sec elapsed 0.04 sec.
INFO: index "blackbox_pkey" now contains 247736 row versions in 1602
pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.01s/0.00u sec elapsed 0.01 sec.
INFO: index "vhost_idx" now contains 247736 row versions in 1226 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.01 sec.
INFO: index "remoteip_idx" now contains 247736 row versions in 682
pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "date_idx" now contains 247736 row versions in 547 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "test_2_idx" now contains 247736 row versions in 682 pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "blackbox": moved 0 row versions, truncated 8436 to 8436 pages
DETAIL: CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_45532"
INFO: "pg_toast_45532": found 0 removable, 0 nonremovable row
versions in 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
Nonremovable row versions range from 0 to 0 bytes long.
There were 0 unused item pointers.
Total free space (including removable row versions) is 0 bytes.
0 pages are or will become empty, including 0 at the end of the table.
0 pages containing 0 free bytes are potential move destinations.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: index "pg_toast_45532_index" now contains 0 row versions in 1
pages
DETAIL: 0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.blackbox"
INFO: "blackbox": scanned 3000 of 8436 pages, containing 87941 live
rows and 0 dead rows; 3000 rows in sample, 247290 estimated total rows
VACUUM
And here the explain results:
logs=> explain select count(*) from blackbox group by day_trunc(ts)
order by day_trunc(ts);
QUERY PLAN
-----------------------------------------------------------------------------
Sort (cost=74210.52..74211.54 rows=407 width=8)
Sort Key: (day_trunc(ts))
-> HashAggregate (cost=74086.04..74192.88 rows=407 width=8)
-> Seq Scan on blackbox (cost=0.00..72847.36 rows=247736
width=8)
(4 rows)
logs=> explain select count(*) from blackbox group by ts order by ts;
QUERY PLAN
------------------------------------------------------------------------------------------
GroupAggregate (cost=0.00..18381.54 rows=77738 width=8)
-> Index Scan using test_2_idx on blackbox (cost=0.00..16171.13
rows=247736 width=8)
(2 rows)
Maybe it’s the silly test queries that prove nothing:
logs=> explain select * from blackbox where day_trunc(ts) =
day_trunc(now());
QUERY PLAN
-------------------------------------------------------------------------------
Index Scan using date_idx on blackbox (cost=0.50..158.65 rows=569
width=237)
Index Cond: (day_trunc(ts) = day_trunc(now()))
(2 rows)
Ciao
--
Giorgio Valoti
