Planner issue

From: Soroosh Sardari <soroosh(dot)sardari(at)gmail(dot)com>
To: PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Planner issue
Date: 2013-10-14 06:21:55
Message-ID: CAFUsPDZ47wjrEsn0yatVsPJw0iuPscqBjm3J5j_w=+kLnHVS-A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Kapila 2013-10-14 06:22:57 Re: Patch for reserved connections for replication users
Previous Message Peter Geoghegan 2013-10-14 04:12:20 Re: INSERT...ON DUPLICATE KEY LOCK FOR UPDATE