Re: Query Plan choice with timestamps

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