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@warped.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,
MinLen88, 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