Fwd: Planner issue - Mailing list pgsql-hackers

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