From: Josh Berkus <josh@agliodbs.com>
To: Michael Nachbaur <mike@nachbaur.com>
To: pgsql-sql@postgresql.org
Subject: Re: Forcing query to use an index
Date: Mon, 3 Mar 2003 15:32:47 -0800
Message-ID: <200303031532.47899.josh@agliodbs.com> (raw)
In-Reply-To: <9959CFC2-4DC7-11D7-90E0-000A27935D5A@nachbaur.com>
References: <9959CFC2-4DC7-11D7-90E0-000A27935D5A@nachbaur.com>
Micheal,
Issues with your query:
LEFT OUTER JOIN Customer_Month_Summary AS CMS
ON ( C.ID = CMS.CustomerID
AND CMS.MonthStart = DATE '2003-02-01'
)
Check out the thread: Re: [SQL] OUTER JOIN with filter
in today's list; this relates to your problem. Then try your query as:
LEFT OUTER JOIN (SELECT * FROM Customer_Month_Summary
WHRE CMS.MonthStart = DATE '2003-02-01'
) CMS ON C.ID = CMS.CustomerID
This may make better use of your index, because the planner will have a more
accurate estimate of the number of rows returned from the outer join.
AND:
AND ( C.Accountnum ~* 'kate'
OR C.Firstname ~* 'kate'
OR C.Lastname ~* 'kate'
OR C.Organization ~* 'kate'
OR C.Address ~* 'kate'
OR C.Postal ~* 'kate'
OR C.City ~* 'kate'
OR EA.Name || '@' || JoinDomain(EA.Domain) ~* 'kate'
This set of expressions has "seq scan" written all over it. I hihgly suggest
that you try to find a way to turn these into anchored text searches, perhaps
using functional indexes on lower(column).
Finally:
OR CMS.Package ~* 'kate'
Per the above, this is why Postgres cannot use an index on your table; that is
an unanchored text search which can *only* be indexed using FTS.
--
-Josh Berkus
Aglio Database Solutions
San Francisco
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: josh@agliodbs.com, mike@nachbaur.com
Subject: Re: Forcing query to use an index
In-Reply-To: <200303031532.47899.josh@agliodbs.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox