pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Max Baker <max@warped.org>
To: Manfred Koizar <mkoi-pg@aon.at>
Cc: PostgreSQL Performance Mailing List <pgsql-performance@postgresql.org>
Subject: Re: speeding up COUNT and DISTINCT queries
Date: Wed, 12 Mar 2003 17:55:40 -0800
Message-ID: <20030313015540.GP30411@warped.org> (raw)
In-Reply-To: <a7hv6v4c6vt4vi2eg8s3dmgg3dskggob84@4ax.com>
References: <20030312223811.GM30411@warped.org>
	<a7hv6v4c6vt4vi2eg8s3dmgg3dskggob84@4ax.com>

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, 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



view thread (14+ messages)  latest in thread

Message-ID: <20030313015540.GP30411@warped.org>
Permalink:  ../20030313015540.GP30411@warped.org/
Also on:    postgresql.org/message-id/20030313015540.GP30411@warped.org

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: max@warped.org, mkoi-pg@aon.at
  Subject: Re: speeding up COUNT and DISTINCT queries
  In-Reply-To: <20030313015540.GP30411@warped.org>
  References: <20030312223811.GM30411@warped.org> <a7hv6v4c6vt4vi2eg8s3dmgg3dskggob84@4ax.com> <20030313015540.GP30411@warped.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox