From: | Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com> |
---|---|
To: | Soroosh Sardari <soroosh(dot)sardari(at)gmail(dot)com> |
Cc: | PostgreSQL Hackers <pgsql-hackers(at)postgresql(dot)org> |
Subject: | Re: Planner issue |
Date: | 2013-10-14 06:59:02 |
Message-ID: | CAFj8pRDxp3P+5-vtdOZUTxNRBJ=ne1jeYXXuURNQ2Rx1GZ_mDg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-hackers |
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(dot)sardari(at)gmail(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Soroosh Sardari | 2013-10-14 07:21:23 | Fwd: Planner issue |
Previous Message | Amit Kapila | 2013-10-14 06:22:57 | Re: Patch for reserved connections for replication users |