From: | Alex Krohn <alex(at)gossamer-threads(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | pgsql-bugs(at)postgresql(dot)org |
Subject: | Re: select fails on indexed varchars. |
Date: | 2001-01-27 01:20:13 |
Message-ID: | 20010126171845.0C11.ALEX@gossamer-threads.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi Tom,
> >> So it does. Okay, what was the complete test case again?
> >> I'm afraid I didn't save your original message because I wrote it off
> >> as a known problem ...
>
> > Here it is:
>
> > links=# create table foo ( a char(25) );
> > CREATE
> > links=# create index foodx on foo (a);
> > CREATE
> > links=# insert into foo values ('Test/Test');
> > INSERT 29689 1
> > links=# select * from foo;
> > a
> > ---------------------------
> > Test/Test
> > (1 row)
>
> > links=# select * from foo where a like 'Test/%';
> > a
> > ---
> > (0 rows)
>
> How odd. I get 'Test/Test' from the last select, under both 7.0.2
> and current sources, when using C locale. The query certainly looks
> like the kind that would suffer from the LIKE-optimization problem in
> non-C locales ... but we seem to have established that you've gotten
> the postmaster switched into C locale.
>
> What does EXPLAIN VERBOSE select * from foo where a like 'Test/%';
> show?
Here's a cut and paste, not sure if there is a nicer way to output it:
links=# EXPLAIN VERBOSE select * from foo where a like 'Test/%';
NOTICE: QUERY DUMP:
{ INDEXSCAN :startup_cost 0.00 :total_cost 8.14 :rows 10 :width 12 :state <> :qptargetlist ({ TARGETENTRY :resdom { RESDOM :resno 1 :restype 1042 :restypmod 29 :resname a :reskey 0 :reskeyop 0 :ressortgroupref 0 :resjunk false } :expr { VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 29 :varlevelsup 0 :varnoold 1 :varoattno 1}}) :qpqual ({ EXPR :typeOid 16 :opType op :oper { OPER :opno 1211 :opid 850 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 29 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 25 :constlen -1 :constisnull false :constvalue 10 [ 10 0 0 0 84 101 115 116 47 37 ] :constbyval false })}) :lefttree <> :righttree <> :extprm () :locprm () :initplan <> :nprm 0 :scanrelid 1 :indxid ( 18825) :indxqual (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 1061 :opid 1052 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 29 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 1042 :constlen -1 :constisnull false :constvalue 9 [ 9 0 0 0 84 101 115 116 47 ] :constbyval false })} { EXPR :typeOid 16 :opType op :oper { OPER :opno 1058 :opid 1049 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 29 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 1042 :constlen -1 :constisnull false :constvalue 9 [ 9 0 0 0 84 101 115 116 48 ] :constbyval false })})) :indxqualorig (({ EXPR :typeOid 16 :opType op :oper { OPER :opno 1061 :opid 1052 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 29 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 1042 :constlen -1 :constisnull false :constvalue 9 [ 9 0 0 0 84 101 115 116 47 ] :constbyval false })} { EXPR :typeOid 16 :opType op :oper { OPER :opno 1058 :opid 1049 :opresulttype 16 } :args ({ VAR :varno 1 :varattno 1 :vartype 1042 :vartypmod 29 :varlevelsup 0 :varnoold 1 :varoattno 1} { CONST :consttype 1042 :constlen -1 :constisnull false :constvalue 9 [ 9 0 0 0 84 101 115 116 48 ] :constbyval false })})) :ind
NOTICE: QUERY PLAN:
Index Scan using foodx on foo (cost=0.00..8.14 rows=10 width=12)
EXPLAIN
links=#
Cheers,
Alex
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2001-01-27 02:34:21 | Re: select fails on indexed varchars. |
Previous Message | Tom Lane | 2001-01-27 01:12:01 | Re: select fails on indexed varchars. |