X-Original-To: pgsql-performance@postgresql.org Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id C6174475957 for ; Fri, 14 Feb 2003 14:22:52 -0500 (EST) Received: from lakemtao01.cox.net (lakemtao01.cox.net [68.1.17.244]) by postgresql.org (Postfix) with ESMTP id 16B334758E6 for ; Fri, 14 Feb 2003 14:22:52 -0500 (EST) Received: from [192.168.0.188] ([68.9.37.244]) by lakemtao01.cox.net (InterMail vM.5.01.04.05 201-253-122-122-105-20011231) with ESMTP id <20030214192252.TVAB16369.lakemtao01.cox.net@[192.168.0.188]>; Fri, 14 Feb 2003 14:22:52 -0500 Subject: Re: [Gmod-schema] Re: performace problem after VACUUM From: Scott Cain To: Tom Lane Cc: pgsql-performance@postgresql.org, gmod schema In-Reply-To: <1045243776.1485.617.camel@localhost.localdomain> References: <1045241040.1486.600.camel@localhost.localdomain> <8000.1045242018@sss.pgh.pa.us> <1045243776.1485.617.camel@localhost.localdomain> Content-Type: text/plain Organization: Cold Spring Harbor Lab Message-Id: <1045250571.1486.625.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 Date: 14 Feb 2003 14:22:51 -0500 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200302/87 X-Sequence-Number: 1143 An update: I ran alter table as suggested, ie, alter table featureloc alter srcfeature_id set statistics 100; on each column in the table, running vacuum analyze and explain analyze on the query in between each alter to see if it made any difference. It did not. Postgres still instists on doing a seq scan on featureloc: Unique (cost=336831.46..337179.45 rows=2320 width=47) (actual time=27219.62..27220.30 rows=179 loops=1) -> Sort (cost=336831.46..336831.46 rows=23200 width=47) (actual time=27219.61..27219.80 rows=186 loops=1) -> Nested Loop (cost=0.00..334732.77 rows=23200 width=47) (actual time=1003.04..27217.99 rows=186 loops=1) -> Seq Scan on featureloc fl (cost=0.00..261709.31 rows=23200 width=14) (actual time=814.68..26094.18 rows=186 loops=1) -> Index Scan using feature_pkey on feature f (cost=0.00..3.14 rows=1 width=33) (actual time=6.03..6.03 rows=1 loops=186) Total runtime: 27220.63 msec On Fri, 2003-02-14 at 12:29, Scott Cain wrote: > Tom, > > Sorry about that: I'll try to briefly give the information you are > looking for. I've read the docs on ALTER TABLE, but it is not clear to > me what columns I should change STATISTICS on, or should I just do it on > all of the columns for which indexes exist? > > Here's the query again: > > select distinct f.name,fl.nbeg,fl.nend,fl.strand,f.type_id,f.feature_id > from feature f, featureloc fl > where > fl.srcfeature_id = 1 and > ((fl.strand=1 and fl.nbeg <= 393164 and fl.nend >= 390956) OR > (fl.strand=-1 and fl.nend <= 393164 and fl.nbeg >= 390956)) and > f.feature_id = fl.feature_id > > -------------------------------------------------------------------------- > > Naive database: > > Unique (cost=75513.46..75513.48 rows=1 width=167) (actual > time=22815.25..22815.93 rows=179 loops=1) > -> Sort (cost=75513.46..75513.46 rows=1 width=167) (actual > time=22815.24..22815.43 rows=186 loops=1) > -> Nested Loop (cost=0.00..75513.45 rows=1 width=167) (actual > time=2471.25..22814.01 rows=186 loops=1) > -> Index Scan using featureloc_idx2 on featureloc fl > (cost=0.00..75508.43 rows=1 width=14) (actual time=2463.83..22796.50 > rows=186 loops=1) > -> Index Scan using feature_pkey on feature f > (cost=0.00..5.01 rows=1 width=153) (actual time=0.08..0.08 rows=1 > loops=186) > Total runtime: 22816.63 msec > -------------------------------------------------------------------------- > > Naive database after featureloc_idx2 dropped: > > Unique (cost=75545.46..75545.48 rows=1 width=167) (actual > time=5232.36..5234.51 rows=179 loops=1) > -> Sort (cost=75545.46..75545.46 rows=1 width=167) (actual > time=5232.35..5232.54 rows=186 loops=1) > -> Nested Loop (cost=0.00..75545.45 rows=1 width=167) (actual > time=291.46..5220.69 rows=186 loops=1) > -> Index Scan using featureloc_src_strand_beg_end on > featureloc fl (cost=0.00..75540.43 rows=1 width=14) (actual > time=291.30..5214.46 rows=186 loops=1) > -> Index Scan using feature_pkey on feature f > (cost=0.00..5.01 rows=1 width=153) (actual time=0.02..0.03 rows=1 > loops=186) > Total runtime: 5234.89 msec > -------------------------------------------------------------------------- > > Database after VACUUM ANALYZE was run: > > Unique (cost=344377.70..344759.85 rows=2548 width=47) (actual > time=26466.82..26467.51 rows=179 loops=1) > -> Sort (cost=344377.70..344377.70 rows=25477 width=47) (actual > time=26466.82..26467.01 rows=186 loops=1) > -> Nested Loop (cost=0.00..342053.97 rows=25477 width=47) > (actual time=262.66..26465.63 rows=186 loops=1) > -> Seq Scan on featureloc fl (cost=0.00..261709.31 > rows=25477 width=14) (actual time=118.62..26006.05 rows=186 loops=1) > -> Index Scan using feature_pkey on feature f > (cost=0.00..3.14 rows=1 width=33) (actual time=2.45..2.46 rows=1 > loops=186) > Total runtime: 26467.85 msec > -------------------------------------------------------------------------- > > After disallowing seqscans (set enable_seqscan=0): > > Unique (cost=356513.46..356895.61 rows=2548 width=47) (actual > time=27494.62..27495.34 rows=179 loops=1) > -> Sort (cost=356513.46..356513.46 rows=25477 width=47) (actual > time=27494.61..27494.83 rows=186 loops=1) > -> Nested Loop (cost=0.00..354189.73 rows=25477 width=47) > (actual time=198.88..27493.48 rows=186 loops=1) > -> Index Scan using featureloc_idx1 on featureloc fl > (cost=0.00..273845.08 rows=25477 width=14) (actual time=129.30..27280.95 > rows=186 loops=1) > -> Index Scan using feature_pkey on feature f > (cost=0.00..3.14 rows=1 width=33) (actual time=1.13..1.13 rows=1 > loops=186) > Total runtime: 27495.66 msec > -------------------------------------------------------------------------- > > After dropping featureloc_idx1: > > Unique (cost=1310195.21..1310577.36 rows=2548 width=47) (actual > time=21692.69..21693.37 rows=179 loops=1) > -> Sort (cost=1310195.21..1310195.21 rows=25477 width=47) (actual > time=21692.69..21692.88 rows=186 loops=1) > -> Nested Loop (cost=0.00..1307871.48 rows=25477 width=47) > (actual time=2197.65..21691.39 rows=186 loops=1) > -> Index Scan using featureloc_idx2 on featureloc fl > (cost=0.00..1227526.82 rows=25477 width=14) (actual > time=2197.49..21618.89 rows=186 loops=1) > -> Index Scan using feature_pkey on feature f > (cost=0.00..3.14 rows=1 width=33) (actual time=0.37..0.38 rows=1 > loops=186) > Total runtime: 21693.72 msec > -------------------------------------------------------------------------- > > After dropping featureloc_idx2: > > Unique (cost=1414516.98..1414899.13 rows=2548 width=47) (actual > time=1669.17..1669.86 rows=179 loops=1) > -> Sort (cost=1414516.98..1414516.98 rows=25477 width=47) (actual > time=1669.17..1669.36 rows=186 loops=1) > -> Nested Loop (cost=0.00..1412193.25 rows=25477 width=47) > (actual time=122.69..1668.08 rows=186 loops=1) > -> Index Scan using featureloc_src_strand_beg_end on > featureloc fl (cost=0.00..1331848.60 rows=25477 width=14) (actual > time=122.51..1661.81 rows=186 loops=1) > -> Index Scan using feature_pkey on feature f > (cost=0.00..3.14 rows=1 width=33) (actual time=0.02..0.03 rows=1 > loops=186) > Total runtime: 1670.20 msec > > > On Fri, 2003-02-14 at 12:00, Tom Lane wrote: > > Scott Cain writes: > > > [ much stuff ] > > > > Could we see EXPLAIN ANALYZE, not just EXPLAIN, output for all these > > alternatives? Your question boils down to "why is the planner > > misestimating these queries" ... which is a difficult question to > > answer when given only the estimates and not the reality. > > > > A suggestion though is that you might need to raise the statistics > > target on the indexed columns, so that ANALYZE will collect > > finer-grained statistics. (See ALTER TABLE ... SET STATISTICS.) > > Try booting it up to 100 (from the default 10), re-analyze, and > > then see if/how the plans change. > > > > regards, tom lane -- ------------------------------------------------------------------------ Scott Cain, Ph. D. cain@cshl.org GMOD Coordinator (http://www.gmod.org/) 216-392-3087 Cold Spring Harbor Laboratory