Home > mailing lists

Thread: Planner issue

Planner issue

From

Soroosh Sardari

Date:

14 October 2013, 09:22:19

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:

14 October 2013, 09:59:50

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:

14 October 2013, 10:21:52

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:

14 October 2013, 10:25:13

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:

14 October 2013, 18:41:36

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