Обсуждение: BUG #4341: planner doesn't using index for = operation
The following bug has been logged online:
Bug reference: 4341
Logged by: Lampa
Email address: lampacz@gmail.com
PostgreSQL version: 8.3.3
Operating system: Debian 2.6.18-6-amd64 #1 SMP Sun Feb 10 17:50:19 UTC
2008 x86_64 GNU/Linux
Description: planner doesn't using index for = operation
Details:
on rodne_cislo is created index with varchar_pattern_ops flag.
With varchar_pattern_ops planner is not using index(first explain) but when
use LIKE index is used.
I must create another index without varchar_pattern_ops flag to get equal
speed results.
explain analyze SELECT * FROM pacienti WHERE rodne_cislo = '8203070007';
QUERY PLAN
----------------------------------------------------------------------------
---------------------------------
Seq Scan on pacienti (cost=0.00..69155.35 rows=2 width=1294) (actual
time=221.901..303.158 rows=1 loops=1)
Filter: ((rodne_cislo)::text = '8203070007'::text)
Total runtime: 303.196 ms
(3 rows)
explain analyze SELECT * FROM pacienti WHERE rodne_cislo LIKE '8203070007';
QUERY PLAN
----------------------------------------------------------------------------
---------------------------------------------------------
Index Scan using i_pacienti_rodne_cislo on pacienti (cost=0.00..12.36
rows=2 width=1294) (actual time=0.032..0.036 rows=1 loops=1)
Index Cond: ((rodne_cislo)::text ~=~ '8203070007'::text)
Filter: ((rodne_cislo)::text ~~ '8203070007'::text)
Total runtime: 0.066 ms
(4 rows)
"Lampa" <lampacz@gmail.com> writes:
> With varchar_pattern_ops planner is not using index(first explain) but when
> use LIKE index is used.
> I must create another index without varchar_pattern_ops flag to get equal
> speed results.
Yup, this is expected behavior because '=' is not part of the
varchar_pattern_ops operator class.
(Things will be better in 8.4, but it's not possible to fix it in
existing release branches.)
regards, tom lane
I was hoping that the a newer version of postgres ( 8.1.11 ) would solve the problem we see when we use order by to get a listing of names. Let me explain the problem. Suppose we have the following 3 names in a table: Ta, A Ta, Z Tab, A I would expect them to show up in the oder shown above when odering by by name, but instead I get: Ta, A Tab, A Ta, Z Which as you can see is not really the desired behavior. I created a test table and loaded these values into a field of type text, and then issued the following query: select * from test order by 1; name=20=20=20=20 -------- Ta, A Tab, A Ta, Z (3 rows) I believe I asked about this when I was on version 7.3.15, and was told this bug would get fixed in a later version. Is there some config parameter I'm not aware of that solves this problem. Thank you! Jose
"Blanco, Jose" <blancoj@umich.edu> writes:
> Suppose we have the following 3 names in a table:
> Ta, A
> Ta, Z
> Tab, A
> I would expect them to show up in the oder shown above when odering by
> by name, but instead I get:
> Ta, A
> Tab, A
> Ta, Z
This is not a bug. Or at least you have not offered any evidence that
suggests that it is a bug. What's more likely is that this is the
standard sort order in the locale you're using, and that you need to
change to a different database locale to get the order you like.
("initdb --locale=C" might be what you want.)
For comparison's sake, I get this on a modern Linux system:
$ cat testdata
Ta, A
Ta, Z
Tab, A
$ LANG=C sort testdata
Ta, A
Ta, Z
Tab, A
$ LANG=en_US sort testdata
Ta, A
Tab, A
Ta, Z
regards, tom lane
Blanco, Jose wrote: > Which as you can see is not really the desired behavior. I created a > test table and loaded these values into a field of type text, and then > issued the following query: > > select * from test order by 1; > name > -------- > Ta, A > Tab, A > Ta, Z > (3 rows) Unlike on some systems, in PostgreSQL "ORDER BY 1" means order by the constant value "1", not the first column. Try "ORDER BY name". -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
Heikki Linnakangas wrote: > Unlike on some systems, in PostgreSQL "ORDER BY 1" means order by the > constant value "1", not the first column. Try "ORDER BY name". Oh, that's of course not correct at all. "ORDER BY 1" does indeed mean order by first column. I don't know what I was thinking, -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
So is there no way to make oder by work with names?=20 -----Original Message----- From: Heikki Linnakangas [mailto:heikki@enterprisedb.com]=20 Sent: Thursday, August 07, 2008 3:00 AM To: Blanco, Jose Cc: pgsql-bugs@postgresql.org Subject: Re: [BUGS] Oder by not working Heikki Linnakangas wrote: > Unlike on some systems, in PostgreSQL "ORDER BY 1" means order by the=20 > constant value "1", not the first column. Try "ORDER BY name". Oh, that's of course not correct at all. "ORDER BY 1" does indeed mean order by first column. I don't know what I was thinking, --=20 Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com
As Tom pointed out, the order depends on the locale. Blanco, Jose wrote: > So is there no way to make oder by work with names? > > -----Original Message----- > From: Heikki Linnakangas [mailto:heikki@enterprisedb.com] > Sent: Thursday, August 07, 2008 3:00 AM > To: Blanco, Jose > Cc: pgsql-bugs@postgresql.org > Subject: Re: [BUGS] Oder by not working > > Heikki Linnakangas wrote: >> Unlike on some systems, in PostgreSQL "ORDER BY 1" means order by the >> constant value "1", not the first column. Try "ORDER BY name". > > Oh, that's of course not correct at all. "ORDER BY 1" does indeed mean > order by first column. I don't know what I was thinking, > -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com