Fwd: Planner issue

> 2013/10/14 Soroosh Sardari <soroosh(dot)sardari(at)gmail(dot)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(dot)stehule(at)gmail(dot)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