Postgres Pro
Downloads
Home   >   mailing lists

Thread: Special meaning of NL string

Special meaning of NL string

From
"Vellinga, Fred"
Date:
Hello,
 
I wonder if there is an issue with the string 'NL'. In my application NL stands for Netherlands and is thus a country code abreviation.
 
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. If I replace OR by AND it also does an index scan. So apparently the string NL in the OR Where clause decides the query planner not to execute an index scan. The database encoding schema is SQL_ASCII.
 
Thanks,
Fred Vellinga
 
 

Re: Special meaning of NL string

From
Tom Lane
Date:
"Vellinga, Fred" <fred.vellinga@nl.verizonbusiness.com> 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

Re: Special meaning of NL string

From
"Vellinga, Fred"
Date:
<p><font size="2">Tom,</font><p><font size="2">Thanks for your answer. The problem is indeed related to the
statistics.</font><br/><font size="2">The isssue now seems to find out why the statistics are 'incorrect' every
day.</font><br/><font size="2">My gues is the following: we run every night the command VACUUM ANALYZE. This command
returnwrong statistics info. Because when I run ANALYZE only on a table, everything is fine.</font><p><font size="2">We
areon PosgreSQL 7.4.</font><br /><p><font size="2">Groet,</font><br /><font size="2">Fred Vellinga</font><br /><br
/><p><fontsize="2">-----Original Message-----</font><br /><font size="2">From: Tom Lane [<a
href="mailto:tgl@sss.pgh.pa.us">mailto:tgl@sss.pgh.pa.us</a>]</font><br /><font size="2">Sent: 11 April 2006
04:58</font><br/><font size="2">To: Vellinga, Fred</font><br /><font size="2">Cc: 'pgsql-sql@postgresql.org'</font><br
/><fontsize="2">Subject: Re: [SQL] Special meaning of NL string </font><br /><p><font size="2">"Vellinga, Fred"
<fred.vellinga@nl.verizonbusiness.com>writes:</font><br /><font size="2">> The query</font><br /><font
size="2">>SELECT COUNT(*) FROM Table WHERE Field1 = 'NL' OR Field2 = 'NL' does a </font><br /><font size="2">>
sequencescan instead of an index scan, and is thus very slow. If I </font><br /><font size="2">> replace NL by BE
(Belgium)the query does an index scan.</font><p><font size="2">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.</font><p>                       
<fontsize="2">regards, tom lane</font>