From: Greg Stark <gsstark@mit.edu>
To: josh@agliodbs.com
Cc: Michael Nachbaur <mike@nachbaur.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Forcing query to use an index
Date: 03 Mar 2003 20:14:11 -0500
Message-ID: <87d6l8hr18.fsf@stark.dyndns.tv> (raw)
In-Reply-To: <200303031532.47899.josh@agliodbs.com>
References: <9959CFC2-4DC7-11D7-90E0-000A27935D5A@nachbaur.com>
<200303031532.47899.josh@agliodbs.com>
One suggestion I'll make about your data model -- I'm not sure it would
actually help this query, but might help elsewhere:
WHERE ( C.Disabled > '2003-02-28'
OR C.Disabled IS NULL
)
Don't use NULL values like this. Most databases don't index NULLs (Oracle) or
even if they do, don't make "IS NULL" an indexable operation (postgres).
There's been some talk of changing this in postgres but even then, it wouldn't
be able to use an index for an OR clause like this.
If you used a very large date, like 9999-01-01 as your "not deactivated" value
then the constraint would be C.disabled > '2003-02-28' and postgres could use
an index on "disabled".
Alternatively if you have a disabled_flag and disabled_date then you could
have an index on disabled_flag,disabled_date and uhm, there should be a way to
use that index though I'm not seeing it right now.
This won't matter at first when 99% of your customers are active. And ideally
in this query you find some way to use an index to find "kate" rather than
doing a fully table scan. But later when 90% of the clients are disabled, then
in a bigger batch job where you actually want to process every active record
it could prevent postgres from having to dig through a table full of old
inactive records.
> 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).
If you really need to find substring matches everywhere you might want to look
into the full text search module in contrib/tsearch. I haven't started using
it yet but I expect I will have to when I get to that part of my project.
> Finally:
>
> OR CMS.Package ~* 'kate'
*confusion*. Oooh, Yeah, this one is a big problem. It means it's not clear
which end of the join to start with. Maybe it would be better to separate this
into two separate queries, give the user the option to search for a user
"kate" or a package "kate" but not both simultaneously.
--
greg
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: gsstark@mit.edu, josh@agliodbs.com, mike@nachbaur.com
Subject: Re: Forcing query to use an index
In-Reply-To: <87d6l8hr18.fsf@stark.dyndns.tv>
* 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