X-Original-To: pgsql-performance@postgresql.org Received: from cat (adsl-66-123-169-52.dsl.sntc01.pacbell.net [66.123.169.52]) by postgresql.org (Postfix) with ESMTP id D3DCF475A4B for ; Wed, 12 Mar 2003 20:55:39 -0500 (EST) Received: by cat (Postfix, from userid 501) id 59C78B6D69; Wed, 12 Mar 2003 17:55:41 -0800 (PST) Date: Wed, 12 Mar 2003 17:55:40 -0800 From: Max Baker To: Manfred Koizar Cc: PostgreSQL Performance Mailing List Subject: Re: speeding up COUNT and DISTINCT queries Message-ID: <20030313015540.GP30411@warped.org> References: <20030312223811.GM30411@warped.org> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: User-Agent: Mutt/1.5.3i X-Archive-Number: 200303/90 X-Sequence-Number: 1388 On Thu, Mar 13, 2003 at 12:48:27AM +0100, Manfred Koizar wrote: > On Wed, 12 Mar 2003 14:38:11 -0800, Max Baker 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