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. :)