> 2013/10/14 Soroosh Sardari <soroosh.sard...@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.steh...@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