Re: [SQL] qurey plan and indices

MESZAROS Attila wrote:
>
> Hi,
>
> I've experienced a brutal speedup (order of 2) separateing the following
> subquery and making it manually:
>
> explain select name,description
> from descriptions
> where in (select name
> from descriptions
> where description like '%Bankverbindung%');
>
> Seq Scan on descriptions (cost=163.98 rows=3575 width=24)
> SubPlan
> -> Seq Scan on descriptions (cost=163.98 rows=2 width=12)
> [I had no patient to wait the resuls...]
>

Correct me if I am wrong, however if using

LIKE '%something%'
(which means, the field contains 'something' somewhere in the field)

there is never a chance to use the index you have created - a
sequential table scan MUST be made, thus you have to read all 3575
rows to return the set.

However, if you change your query so that you are looking for

LIKE 'Bankverbindung%'

then an index can be used becase you know what the first few
characters are known.

Hope this helps,

Cordially

Patrick Giagnocavo
a222(at)redrose(dot)net