Re: Query Plan choice with timestamps - Mailing list pgsql-performance
From | Giorgio Valoti |
---|---|
Subject | Re: Query Plan choice with timestamps |
Date | |
Msg-id | 2694B344-E979-43A9-B11A-0556DF274A6F@mac.com Whole thread Raw |
In response to | Re: Query Plan choice with timestamps (Giorgio Valoti <giorgio_v@mac.com>) |
List | pgsql-performance |
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
pgsql-performance by date: