Thread: Query planner issue

Hi there,

   I am currently running Postgres 8.3.1.

I've got a table called DETAILS, the primary key is : DE_ID char(12),
there is another field CO_ID char (12).
DE_ID and CO_ID are indexed with a btree. This table is about 140
millions of records.

If I execute an "explain select * from details where co_id =
'010076015372';" it uses the index. Here follows the plan:

"Index Scan using idx_co_id on details  (cost=0.00..34.37 rows=2
width=741)"
"  Index Cond: ((co_id)::bpchar = '010076015372'::bpchar)"


If I run "explain analyze select * from details where co_id || co_id =
'0100760153722324445';" it runs a sequential scan not using the index
(which takes about 1000000 times than using the index):

  Seq Scan on details  (cost=0.00..8755139.52 rows=819131 width=741)
    Filter: (((co_id)::text || (co_id)::text) =
'010076015372010076015372'::text)

Same thing if I try to trick it using a view or a stored procedure.
Query planner is not easy to trick!

My question is : is there a way to use the index or isn't it possible?

Thanks in advance.

Andrea Moretto

Andrea Moretto
moretto.andrea@gmail.com
-------------------------------------------------------
CONFIDENTIALITY NOTICE
This message and its attachments are addressed solely to the persons
above and may contain confidential information. If you have received
the message in error, be informed that any use of the content hereof
is prohibited. Please return it immediately to the sender and delete
the message.
-------------------------------------------------------

Andrea Moretto wrote:
> I've got a table called DETAILS, the primary key is : DE_ID char(12),
> there is another field CO_ID char (12).

> If I run "explain analyze select * from details where co_id || co_id =
> '0100760153722324445';" it runs a sequential scan not using the index
> (which takes about 1000000 times than using the index):

That query can never return any rows. You're appending co_id to itself
then testing against a non-repeating string. Furthermore it's 19 chars
long rather than 24. Is this really what you're trying to do?

Read up on "indexes on expressions" in chapter 11 of the manuals, for
indexing this sort of thing. I don't think it will help you here though.

--
  Richard Huxton
  Archonet Ltd

Dear Richard,

   you are right. So Postgres takes a lot of time to get out an
impossible result!
The query I wrote is just a test, the concatenation is only a dummy.

On 19/set/08, at 12:33, Richard Huxton wrote:

> Andrea Moretto wrote:
>> I've got a table called DETAILS, the primary key is : DE_ID char(12),
>> there is another field CO_ID char (12).
>
>> If I run "explain analyze select * from details where co_id ||
>> co_id =
>> '0100760153722324445';" it runs a sequential scan not using the index
>> (which takes about 1000000 times than using the index):
>
> That query can never return any rows. You're appending co_id to itself
> then testing against a non-repeating string. Furthermore it's 19 chars
> long rather than 24. Is this really what you're trying to do?
>
> Read up on "indexes on expressions" in chapter 11 of the manuals, for
> indexing this sort of thing. I don't think it will help you here
> though.
>
> --
>  Richard Huxton
>  Archonet Ltd

Andrea Moretto
moretto.andrea@gmail.com
-------------------------------------------------------
CONFIDENTIALITY NOTICE
This message and its attachments are addressed solely to the persons
above and may contain confidential information. If you have received
the message in error, be informed that any use of the content hereof
is prohibited. Please return it immediately to the sender and delete
the message.
-------------------------------------------------------

As Richard already pointed out the query issues, I'll point out the
low-hanging fruit.

At 6:17am -0400 on Fri, 19 Sep 2008, Andrea Moretto wrote:
> I am currently running Postgres 8.3.1.
                                  ^^^^^
The current minor release is 8.3.3 and 8.3.4 is expected Monday.

    Upgrade.

You gain absolutely nothing staying at an out-of-date minor revision.

Kevin

On Fri, Sep 19, 2008 at 4:17 AM, Andrea Moretto
<moretto.andrea@gmail.com> wrote:
> Hi there,
>
>  I am currently running Postgres 8.3.1.
>
> I've got a table called DETAILS, the primary key is : DE_ID char(12), there
> is another field CO_ID char (12).
> DE_ID and CO_ID are indexed with a btree. This table is about 140 millions
> of records.
>
> If I execute an "explain select * from details where co_id =
> '010076015372';" it uses the index. Here follows the plan:
>
> "Index Scan using idx_co_id on details  (cost=0.00..34.37 rows=2 width=741)"
> "  Index Cond: ((co_id)::bpchar = '010076015372'::bpchar)"
>
>
> If I run "explain analyze select * from details where co_id || co_id =
> '0100760153722324445';" it runs a sequential scan not using the index (which
> takes about 1000000 times than using the index):

So, let's assume you have a real use case not this test on, like

select * from db where field1||field2 = 'abc123';

where field1=abc and field2=123 (and they're all text).

create index myinsaneindex on table ((field1||field2));

now if you use a where clause like the above you should be able to get
an index scan.

Look up functional and partial indexes.  PostgreSQL isn't real good at
getting you out of your own created bad situations, but it is very
good at providing you with the tools to do it yourself. :)