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
Show quoted text
-----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 questionIs 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
valuesI 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 = 12345The 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 elsePS Is there a way of finding out which indexes exist on a table???
Thanks in advance,
MC.