Обсуждение: 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.


--
NOTICE:  The information contained in this electronic mail transmission is
intended by Convergys Corporation for the use of the named individual or entity
to which it is directed and may contain information that is privileged or
otherwise confidential.  If you have received this electronic mail transmission
in error, please delete it from your system without copying or forwarding it,
and notify the sender of the error by reply email or by telephone (collect), so
that the sender's address records can be corrected.

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@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of
> martin.chantler@convergys.com
> Sent: 08 December 2000 11:02
> To: pgsql-general@postgresql.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.
>