Re: speeding up COUNT and DISTINCT queries

On Thu, Mar 13, 2003 at 12:48:27AM +0100, Manfred Koizar wrote:
> On Wed, 12 Mar 2003 14:38:11 -0800, Max Baker <max(at)warped(dot)org> wrote:
> > -> Seq Scan on node (cost=0.00..107737.61 rows=35561
> > width=6) (actual time=6.73..44383.57 rows=34597 loops=1)
>
> 35000 tuples in 100000 pages?
>
> >I run VACCUUM ANALYZE once a day.
>
> Try VACUUM FULL VERBOSE ANALAYZE; this should bring back your table
> to a reasonable size. If the table starts growing again, VACUUM more
> often.

Manfred,

Thanks for the help. I guess i'm not clear on why there is so much
extra cruft. Does postgres leave a little bit behind every time it does
an update? Because this table is updated constantly.

Check out the results, 1.5 seconds compared to 46 seconds :

mydb=> vacuum full verbose analyze node;
NOTICE: --Relation node--
NOTICE: Pages 107589: Changed 0, reaped 107588, Empty 0, New 0; Tup 34846: Vac 186847, Keep/VTL 0/0, UnUsed 9450103, MinLen 88, MaxLen 104; Re-using: Free/Avail. Space 837449444/837449368; EndEmpty/Avail. Pages 0/107588.
CPU 15.32s/0.51u sec elapsed 30.89 sec.
NOTICE: Index node_pkey: Pages 10412; Tuples 34846: Deleted 186847.
CPU 3.67s/2.48u sec elapsed 77.06 sec.
NOTICE: Index idx_node_switch_port: Pages 54588; Tuples 34846: Deleted 186847.
CPU 9.59s/2.42u sec elapsed 273.50 sec.
NOTICE: Index idx_node_switch: Pages 50069; Tuples 34846: Deleted 186847.
CPU 8.46s/2.08u sec elapsed 258.62 sec.
NOTICE: Index idx_node_mac: Pages 6749; Tuples 34846: Deleted 186847.
CPU 2.19s/1.59u sec elapsed 56.05 sec.
NOTICE: Index idx_node_switch_port_active: Pages 51138; Tuples 34846: Deleted 186847.
CPU 8.58s/2.99u sec elapsed 273.03 sec.
NOTICE: Index idx_node_mac_active: Pages 6526; Tuples 34846: Deleted 186847.
CPU 1.75s/1.90u sec elapsed 46.70 sec.
NOTICE: Rel node: Pages: 107589 --> 399; Tuple(s) moved: 34303.
CPU 83.49s/51.73u sec elapsed 1252.35 sec.
NOTICE: Index node_pkey: Pages 10412; Tuples 34846: Deleted 34303.
CPU 3.65s/1.64u sec elapsed 72.99 sec.
NOTICE: Index idx_node_switch_port: Pages 54650; Tuples 34846: Deleted 34303.
CPU 10.77s/2.05u sec elapsed 278.46 sec.
NOTICE: Index idx_node_switch: Pages 50114; Tuples 34846: Deleted 34303.
CPU 9.95s/1.65u sec elapsed 266.55 sec.
NOTICE: Index idx_node_mac: Pages 6749; Tuples 34846: Deleted 34303.
CPU 1.75s/1.13u sec elapsed 52.78 sec.
NOTICE: Index idx_node_switch_port_active: Pages 51197; Tuples 34846: Deleted 34303.
CPU 10.48s/1.89u sec elapsed 287.46 sec.
NOTICE: Index idx_node_mac_active: Pages 6526; Tuples 34846: Deleted 34303.
CPU 2.16s/0.96u sec elapsed 48.67 sec.
NOTICE: --Relation pg_toast_64458--
NOTICE: Pages 0: Changed 0, reaped 0, Empty 0, New 0; Tup 0: Vac 0, Keep/VTL 0/0, UnUsed 0, MinLen 0, MaxLen 0; Re-using: Free/Avail. Space 0/0; EndEmpty/Avail. Pages 0/0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Index pg_toast_64458_idx: Pages 1; Tuples 0.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
NOTICE: Analyzing node
VACUUM

mydb=> EXPLAIN ANALYZE select distinct(mac) from node;
NOTICE: QUERY PLAN:

Unique (cost=3376.37..3463.48 rows=3485 width=6) (actual time=1049.09..1400.45 rows=25340 loops=1)
-> Sort (cost=3376.37..3376.37 rows=34846 width=6) (actual time=1049.07..1190.58 rows=34846 loops=1)
-> Seq Scan on node (cost=0.00..747.46 rows=34846 width=6) (actual time=0.14..221.18 rows=34846 loops=1)
Total runtime: 1491.56 msec

EXPLAIN

now that's results =]
-m