X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (mx2.hub.org [200.46.204.254]) by postgresql.org (Postfix) with ESMTP id 815F311F6C54 for ; Thu, 13 Apr 2006 03:44:50 -0300 (ADT) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Score: -1.967 X-Spam-Level: X-Spam-Status: No, score=-1.967 required=5 tests=[BAYES_00=-2.599, FORGED_RCVD_HELO=0.135, HTML_40_50=0.496, HTML_MESSAGE=0.001] Received: from postgresql.org ([200.46.204.71]) by localhost (mx2.hub.org [200.46.204.254]) (amavisd-new, port 10024) with ESMTP id DdRBficliaDW for ; Thu, 13 Apr 2006 03:44:47 -0300 (ADT) X-Greylist: delayed 00:21:01.863806 by SQLgrey- Received: from svr4.postgresql.org (svr4.postgresql.org [66.98.251.159]) by postgresql.org (Postfix) with ESMTP id BC47911F6B99 for ; Thu, 13 Apr 2006 03:44:42 -0300 (ADT) Received: from sirius.emea.verizonbusiness.com (sirius.wcom.co.uk [193.131.254.154]) by svr4.postgresql.org (Postfix) with ESMTP id BA29F5AF927 for ; Thu, 13 Apr 2006 06:23:41 +0000 (GMT) Received: from ocampa.wcom.co.uk ([166.59.189.250] helo=borg.emea.verizonbusiness.com) by sirius.emea.verizonbusiness.com with esmtp (Exim 4.42) id 1FTvEu-000423-7c; Thu, 13 Apr 2006 06:23:37 +0000 Received: from ms-lon-exgw01.uk.mcilink.com ([170.127.78.40]) by borg.emea.verizonbusiness.com with esmtp (Exim 4.42) id 1FTvEt-0004qZ-GC; Thu, 13 Apr 2006 06:23:35 +0000 Received: by ms-lon-exgw01.uk.mcilink.com with Internet Mail Service (5.5.2657.72) id <2QP2DHRA>; Thu, 13 Apr 2006 07:23:34 +0100 Message-ID: From: "Vellinga, Fred" To: 'Tom Lane' Cc: "'pgsql-sql@postgresql.org'" Subject: Re: Special meaning of NL string Date: Thu, 13 Apr 2006 07:23:56 +0100 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-Type: multipart/alternative; boundary="----_=_NextPart_001_01C65EC1.E0877C74" X-VZ-EMEA-Spam-Score: -498.0 (---------------------------------------------------) X-VZ-EMEA-Signature: 4026bec7cd6ad7aee0e118a55d4baa04 X-Archive-Number: 200604/105 X-Sequence-Number: 24914 This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. ------_=_NextPart_001_01C65EC1.E0877C74 Content-Type: text/plain Tom, Thanks for your answer. The problem is indeed related to the statistics. The isssue now seems to find out why the statistics are 'incorrect' every day. My gues is the following: we run every night the command VACUUM ANALYZE. This command return wrong statistics info. Because when I run ANALYZE only on a table, everything is fine. We are on PosgreSQL 7.4. Groet, Fred Vellinga -----Original Message----- From: Tom Lane [mailto:tgl@sss.pgh.pa.us] Sent: 11 April 2006 04:58 To: Vellinga, Fred Cc: 'pgsql-sql@postgresql.org' Subject: Re: [SQL] Special meaning of NL string "Vellinga, Fred" writes: > The query > SELECT COUNT(*) FROM Table WHERE Field1 = 'NL' OR Field2 = 'NL' does a > sequence scan instead of an index scan, and is thus very slow. If I > replace NL by BE (Belgium) the query does an index scan. Probably, 'NL' is a lot more common than 'BE' in your table ... the planner does examine statistics while deciding what sort of scan to use. regards, tom lane ------_=_NextPart_001_01C65EC1.E0877C74 Content-Type: text/html Content-Transfer-Encoding: quoted-printable RE: [SQL] Special meaning of NL string

Tom,

Thanks for your answer. The problem is indeed related = to the statistics.
The isssue now seems to find out why the statistics = are 'incorrect' every day.
My gues is the following: we run every night the = command VACUUM ANALYZE. This command return wrong statistics info. = Because when I run ANALYZE only on a table, everything is = fine.

We are on PosgreSQL 7.4.


Groet,
Fred Vellinga



-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: 11 April 2006 04:58
To: Vellinga, Fred
Cc: 'pgsql-sql@postgresql.org'
Subject: Re: [SQL] Special meaning of NL string =


"Vellinga, Fred" = <fred.vellinga@nl.verizonbusiness.com> writes:
> The query
> SELECT COUNT(*) FROM Table WHERE Field1 =3D = 'NL' OR Field2 =3D 'NL' does a
> sequence scan instead of an index scan, and is = thus very slow. If I
> replace NL by BE (Belgium) the query does an = index scan.

Probably, 'NL' is a lot more common than 'BE' in your = table ... the planner does examine statistics while deciding what sort = of scan to use.

        =         =         regards, tom = lane

------_=_NextPart_001_01C65EC1.E0877C74--