pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSpecial meaning of NL string
3+ messages / 2 participants
[nested] [flat]
* Special meaning of NL string
@ 2006-04-04 08:40 Vellinga, Fred <fred.vellinga@nl.verizonbusiness.com>
0 siblings, 1 reply; 3+ messages in thread
From: Vellinga, Fred @ 2006-04-04 08:40 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Special meaning of NL string
@ 2006-04-11 02:57 Tom Lane <tgl@sss.pgh.pa.us>
parent: Vellinga, Fred <fred.vellinga@nl.verizonbusiness.com>
0 siblings, 0 replies; 3+ messages in thread
From: Tom Lane @ 2006-04-11 02:57 UTC (permalink / raw)
To: Vellinga, Fred <fred.vellinga@nl.verizonbusiness.com>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Special meaning of NL string
@ 2006-04-13 06:23 Vellinga, Fred <fred.vellinga@nl.verizonbusiness.com>
0 siblings, 0 replies; 3+ messages in thread
From: Vellinga, Fred @ 2006-04-13 06:23 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
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 = '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
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2006-04-13 06:23 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-04-04 08:40 Special meaning of NL string Vellinga, Fred <fred.vellinga@nl.verizonbusiness.com>
2006-04-11 02:57 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-04-13 06:23 Re: Special meaning of NL string Vellinga, Fred <fred.vellinga@nl.verizonbusiness.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox