X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 3D1C95E46CF for ; Fri, 20 Aug 2004 13:13:05 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 77298-04 for ; Fri, 20 Aug 2004 16:13:09 +0000 (GMT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by svr1.postgresql.org (Postfix) with ESMTP id 1DB725E46C0 for ; Fri, 20 Aug 2004 13:13:01 -0300 (ADT) Received: from news.hub.org (news.hub.org [200.46.204.72]) by news.hub.org (8.12.9/8.12.9) with ESMTP id i7KGD8iG078962 for ; Fri, 20 Aug 2004 16:13:08 GMT (envelope-from news@news.hub.org) Received: (from news@localhost) by news.hub.org (8.12.9/8.12.9/Submit) id i7KFqgdw071052 for pgsql-performance@postgresql.org; Fri, 20 Aug 2004 15:52:42 GMT From: Gaetano Mendola X-Newsgroups: comp.databases.postgresql.performance Subject: Re: using an index worst performances Date: Fri, 20 Aug 2004 17:52:33 +0200 Organization: PYRENET Midi-pyrenees Provider Lines: 80 Message-ID: <41261E41.3080204@bigfoot.com> References: <4124ECF3.6040901@archonet.com> <412505F6.6080000@bigfoot.com> <4125565D.3060008@familyhealth.com.au> <4125C657.7000702@bigfoot.com> <1093006549.75942.61.camel@jester> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Complaints-To: abuse@pyrenet.fr To: Rod Taylor User-Agent: Mozilla Thunderbird 0.7.3 (Windows/20040803) X-Accept-Language: en-us, en In-Reply-To: <1093006549.75942.61.camel@jester> X-Enigmail-Version: 0.85.0.0 X-Enigmail-Supports: pgp-inline, pgp-mime To: pgsql-performance@postgresql.org X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200408/302 X-Sequence-Number: 7938 Rod Taylor wrote: > On Fri, 2004-08-20 at 05:37, Gaetano Mendola wrote: > >>-----BEGIN PGP SIGNED MESSAGE----- >>Hash: SHA1 >> >>Christopher Kings-Lynne wrote: >> >>|>>> Without index: 1.140 ms >>|>>> With index: 1.400 ms >>|>>> With default_statistic_targer = 200: 1.800 ms >>|>> >>|>> >>|>> >>|>> >>|>> Can I just check that 1.800ms means 1.8 secs (You're using . as the >>|>> thousands separator)? >>|>> >>|>> If it means 1.8ms then frankly the times are too short to mean >>|>> anything without running them 100 times and averaging. >>|> >>|> >>|> >>|> >>|> It mean 1.8 ms and that execution time is sticky to that value even >>|> with 1000 times. >>| >>| >>| Given the almost irrelvant difference in the speed of those queries, I'd >>| say that with the stats so high, postgres simply takes longer to check >>| the statistics to come to the same conclusion. ie. it has to loop over >>| 200 rows instead of just 10. >> >>The time increase seems too much. > > > We can test this. > > What are the times without the index, with the index and with the higher > statistics value when using a prepared query? Using a prepared query: Without index and default stat 10 : 1.12 ms Without index and default stat 1000 : 1.25 ms With index and default stat 10: 1.35 ms With index and default stat 1000: 1.6 ms that values are the average obtained after the very first one, on 20 execution. Regards Gaetano Mendola