Mitchell Vincent <[EMAIL PROTECTED]>
wrote:
Is there any way to determine if a query is using an index or not? In
PostgreSQL the "explain" works to tell whether a table is being
sequentially scanned or not..
Prepend the query with EXPLAIN QUERY PLAN
I have a query :
SELECT *,(total - balance_due) as total_paid FROM invoice_master WHERE
lower(invoice_number) LIKE lower('%%') AND status != 'Void' AND
status != 'Recur' AND status != 'Paid' AND status != 'Forwarded'
ORDER BY created ASC LIMIT 25
The lower('%%') gets used with whatever field the user is searching
on.
I have indexes on created, status and invoice_number - but apparently
I can't make an index on lower(invoice_number) -- can I?
You can't. You can, however, create an index on invoice_number with
COLLATE NOCASE clause.
Note that condition "lower(invoice_number) LIKE lower('%%')" is always
true - any string matches this pattern. What precisely is this supposed
to achieve, and how an index on lower(invoice_number) is expected to
help here?
A condition on status can be more compactly written as
status NOT IN ('Void', 'Recur', 'Paid', 'Forwarded')
The query as written should use an index on "created" to implement ORDER
BY clause.
Igor Tandetnik
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
