RE: Index question

Hi Martin,

You could try doing this:

SET ENABLE_SEQSCAN OFF

... and then run EXPLAIN with your SQL statement again and compare the
two costs. That may well show you why the sequential scan is chosen over
the index scan.

Also, have you VACUUM ANALYZE'd your tables recently?

You can find out which indexes exist on a table from psql by doing \d
<tablename> and the characteristics of that index by doing \d
<indexname>

The actual query to retrieve the names of the indices (as reported by
'psql -E') is:
SELECT
c2.relname
FROM
pg_class c, pg_class c2, pg_index i
WHERE
c.relname = 'users' AND
c.oid = i.indrelid AND
i.indexrelid = c2.oid
ORDER BY
c2.relname;

Hope this helps

Francis Solomon

> -----Original Message-----
> From: pgsql-general-owner(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of
> martin(dot)chantler(at)convergys(dot)com
> Sent: 08 December 2000 11:02
> To: pgsql-general(at)postgresql(dot)org
> Subject: [GENERAL] Index question
>
>
>
>
> Is there any reason an index would not be used in optimising a query?
> I have an SQL which does a join and correctly picks up an
> index on a numeric
> column
> but fails to use another index that is on a character column
> (char I think)
> Maybe its because its character column - as most joins are
> done on numeric
> values
>
> I just wondered if theres a simple answer to this? If not
> I'll post the SQL
> which I don't have
> with me!
>
> I think the SQL went something like this:
>
> Select * From Order JOIN Customer ON Customer.account_no =
> Order.account_no
> WHERE Order.order_no = 12345
>
> The explain function told me it was using the index on
> order_no but scanning the
> customer
> table!
>
> Either a) I've made a stupid mistake
> or b) Something else
>
> PS Is there a way of finding out which indexes exist on a table???
>
>
> Thanks in advance,
>
> MC.
>