Planner issue

Hello

pls, send a output of EXPLAIN ANALYZE statement,

there can be different reasons why optimizer doesn't choose some index

Regards

Pavel Stehule

2013/10/14 Soroosh Sardari <soroosh.sardari@gmail.com>

2013/10/14 Soroosh Sardari <soroosh.sardari@gmail.com>

Hi

I developed a new character string type, named myvarchar.
Also an operator class for btree is added.

I created a table with two columns, first have myvarchar(100) and other
is
varchar(100).

CREATE TABLE test_myvarchar (mine myvarchar(100), plain varchar(100));

CREATE INDEX test_myvarchar_i_mine ON test_myvarchar USING btree (mine);
CREATE INDEX test_myvarchar_i_plain ON test_myvarchar USING btree (plain);

Two same random strings to both of columns are inserted, and the
operation repeated until 32K rows are in the table.

INSERT INTO test_myvarchar VALUES ('example', 'example');

PROBLEM:
When I executed a query with where clause on 'mine' column, PG does not
use index.
But after I changed where clause to be on 'plain' column, PG uses index!

EXPLAIN SELECT * FROM test_myvarchar WHERE 'zagftha' >= mine ORDER BY 1;
----------------------
Sort (cost=3038.39..3065.00 rows=10642 width=197)
Sort Key: mine
-> Seq Scan on test_myvarchar (cost=0.00..1308.08 rows=10642
width=197)
Filter: ('zagftha'::myvarchar >= mine)

##############################################
EXPLAIN SELECT * FROM test_myvarchar WHERE 'zagftha' >= plain ORDER BY 2;

Index Scan using test_myvarchar_i_plain on test_myvarchar
(cost=0.41..6099.0
8 rows=31175 width=197)
Index Cond: ('zagftha'::text >= (plain)::text)

Why planner does not choose the lowest cost path?
Is there any problem with my new type? How can I fix it?

Any help would be appreciated.

Regards,
Soroosh Sardari
Sharif University of Technology

On Mon, Oct 14, 2013 at 10:29 AM, Pavel Stehule <pavel.stehule@gmail.com>
wrote:

Hello

pls, send a output of EXPLAIN ANALYZE statement,

there can be different reasons why optimizer doesn't choose some index

Regards

Pavel Stehule

The output of EXPLAIN ANALYSE for the two queries come in the blow.

Sort (cost=3038.39..3065.00 rows=10642 width=197) (actual
time=938.564..1168.1
18 rows=31070 loops=1)
Sort Key: mine
Sort Method: external merge Disk: 6304kB
-> Seq Scan on test_myvarchar (cost=0.00..1308.08 rows=10642
width=197) (a
ctual time=0.072..78.545 rows=31070 loops=1)
Filter: ('zagftha'::myvarchar >= mine)
Rows Removed by Filter: 856
Total runtime: 1176.822 ms

Index Scan using test_myvarchar_i_plain on test_myvarchar
(cost=0.41..6099.0
8 rows=31175 width=197) (actual time=0.124..61.417 rows=31054 loops=1)
Index Cond: ('zagftha'::text >= (plain)::text)
Total runtime: 67.918 ms

Soroosh Sardari <soroosh.sardari@gmail.com> writes:

I developed a new character string type, named myvarchar.
Also an operator class for btree is added.
PROBLEM:
When I executed a query with where clause on 'mine' column, PG does not use
index.

Most likely you got the opclass definition wrong. Since you've shown us
no details of what you did, it's hard to speculate about just how. But
note that varchar itself is a pretty bad model for a user-added datatype,
because it has a special symbiotic relationship with type "text" (to wit,
it has no operators of its own but uses text's operators via implicit
casts). To get to a working independent datatype like this, you'd need
to pick the right aspects of each of text and varchar to clone. So my
unfounded speculation is you didn't do that just right.

regards, tom lane

--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On Mon, Oct 14, 2013 at 10:55 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Soroosh Sardari <soroosh.sardari@gmail.com> writes:

I developed a new character string type, named myvarchar.
Also an operator class for btree is added.
PROBLEM:
When I executed a query with where clause on 'mine' column, PG does not

use

index.

Most likely you got the opclass definition wrong. Since you've shown us
no details of what you did, it's hard to speculate about just how. But
note that varchar itself is a pretty bad model for a user-added datatype,
because it has a special symbiotic relationship with type "text" (to wit,
it has no operators of its own but uses text's operators via implicit
casts). To get to a working independent datatype like this, you'd need
to pick the right aspects of each of text and varchar to clone. So my
unfounded speculation is you didn't do that just right.

regards, tom lane

As Tom said, I did something wrong when I was creating new operators.
The RESTRICT parameter is forgotten. Since all varchar operations
redirected to text operators, hence my operators must be like
operators of type text.

I used following command to find text operator:

select * from pg_operator where oprleft = 25
and oprright = 25

P.S : 25 is oid of text type.

Cheers,
Soroosh Sardari