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.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 >