Обсуждение: Planner issue
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?
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
Hello
pls, send a output of EXPLAIN ANALYZE statement,Pavel Stehule
2013/10/14 Soroosh Sardari <soroosh.sardari@gmail.com>
HiI 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 isvarchar(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.08 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 SardariSharif University of Technology
HiI 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 isvarchar(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.08 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 SardariSharif University of Technology
On Mon, Oct 14, 2013 at 10:29 AM, Pavel Stehule <pavel.stehule@gmail.com> wrote:
Regardsthere can be different reasons why optimizer doesn't choose some indexHellopls, send a output of EXPLAIN ANALYZE statement,
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
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
<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>