Thread: Planner issue

Planner issue

From
Soroosh Sardari
Date:
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

Re: Planner issue

From
Pavel Stehule
Date:
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.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

Fwd: Planner issue

From
Soroosh Sardari
Date:

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


On Mon, Oct 14, 2013 at 10:29 AM, Pavel Stehule <pavel.stehule@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 

Re: Planner issue

From
Tom Lane
Date:
Soroosh Sardari <soroosh.sardari@gmail.com> writes:
> I developed a new character string type, named myvarchar.
> Also an operator class for btree is added.
> PROBLEM:
> When I executed a query with where clause on 'mine' column, PG does not use
> index.

Most likely you got the opclass definition wrong.  Since you've shown us
no details of what you did, it's hard to speculate about just how.  But
note that varchar itself is a pretty bad model for a user-added datatype,
because it has a special symbiotic relationship with type "text" (to wit,
it has no operators of its own but uses text's operators via implicit
casts).  To get to a working independent datatype like this, you'd need
to pick the right aspects of each of text and varchar to clone.  So my
unfounded speculation is you didn't do that just right.
        regards, tom lane



Re: Planner issue

From
Soroosh Sardari
Date:
<div dir="ltr"><br /><div class="gmail_extra"><br /><br /><div class="gmail_quote">On Mon, Oct 14, 2013 at 10:55 AM,
TomLane <span dir="ltr"><<a href="mailto:tgl@sss.pgh.pa.us" target="_blank">tgl@sss.pgh.pa.us</a>></span>
wrote:<br/><blockquote class="gmail_quote" style="margin:0px 0px 0px
0.8ex;border-left-width:1px;border-left-color:rgb(204,204,204);border-left-style:solid;padding-left:1ex"><div
class="im">SorooshSardari <<a href="mailto:soroosh.sardari@gmail.com">soroosh.sardari@gmail.com</a>> writes:<br
/>> I developed a new character string type, named myvarchar.<br /> > Also an operator class for btree is
added.<br/></div><div class="im">> PROBLEM:<br /> > When I executed a query with where clause on 'mine' column,
PGdoes not use<br /> > index.<br /><br /></div>Most likely you got the opclass definition wrong.  Since you've shown
us<br/> no details of what you did, it's hard to speculate about just how.  But<br /> note that varchar itself is a
prettybad model for a user-added datatype,<br /> because it has a special symbiotic relationship with type "text" (to
wit,<br/> it has no operators of its own but uses text's operators via implicit<br /> casts).  To get to a working
independentdatatype like this, you'd need<br /> to pick the right aspects of each of text and varchar to clone.  So
my<br/> unfounded speculation is you didn't do that just right.<br /><br />                         regards, tom
lane<br/></blockquote></div><br /></div><div class="gmail_extra"><br /></div><div class="gmail_extra" style="style">As
Tomsaid, I did something wrong when I was creating new operators.</div><div class="gmail_extra" style="style">The
RESTRICTparameter is forgotten. Since all varchar operations</div><div class="gmail_extra" style="style">redirected to
textoperators, hence my operators must be like</div><div class="gmail_extra" style="style">operators of type
text.</div><divclass="gmail_extra" style="style"><br /></div><div class="gmail_extra" style="style"> I used following
commandto find text operator:</div><div class="gmail_extra" style="style"><div class="gmail_extra"><br /></div><div
class="gmail_extra">select* from pg_operator  where oprleft = 25 </div><div class="gmail_extra"><span class=""
style="white-space:pre"></span>and oprright = 25</div></div><div class="gmail_extra" style="style"><br /></div><div
class="gmail_extra"style="style">P.S : 25 is oid of text type.</div><div class="gmail_extra" style="style"><br
/></div><divclass="gmail_extra" style="style">Cheers,</div><div class="gmail_extra" style="style">Soroosh
Sardari</div></div>