Обсуждение: Not using index

Hi,
I have a table with about 8million tuples in it. I need to do a search
on two of the table's columns, so I've created a couple of indices, one
for each column.
Then I've run VACUUM ANALYZE. The query planner still wants to do a
sequential scan on the database. Any idea where I'm going wrong?
Crispin

Crispin Miller <crispin@cs.man.ac.uk> writes:
> Then I've run VACUUM ANALYZE. The query planner still wants to do a
> sequential scan on the database. Any idea where I'm going wrong?

Not providing enough detail to let anyone help you ;-)

Let's see the table schema (pg_dump -s -t table is a useful way to
collect the info quickly and accurately), the attempted query, and
the EXPLAIN output for it.  Also, if you do
    SET enable_seqscan TO OFF;
how does that change the EXPLAIN result?

            regards, tom lane

At 14:47 07.02.01 +0000, you wrote:
>Hi,
>I have a table with about 8million tuples in it. I need to do a search
>on two of the table's columns, so I've created a couple of indices, one
>for each column.
>Then I've run VACUUM ANALYZE. The query planner still wants to do a
>sequential scan on the database. Any idea where I'm going wrong?
>Crispin

For this, you need to create an index on BOTH columns, e.g.

'create index foo on bar ( baz, bang )'

That should help.

Greetings,
         Joerg

Hi Tom, Jeorg,
Thanks for the help - I thought I might be being a bit vague...
Jeorg's suggestion solved my problem - I need to create an index across
both  columns:

'create index foo on bar ( baz, bang )'

Like what Jeorg said I should :-)

Appreciated,
Crispin