From: Randolf Richardson, DevNet SysOp 29 <rr@8x.ca>
To: pgsql-sql@postgresql.org
Subject: Re: Expressional Indexes
Date: Fri, 21 Nov 2003 19:26:31 +0000 (UTC)
Message-ID: <Xns943A725C1BA38rr8xca@200.46.204.72> (raw)
References: <7141E2A2-04BE-11D8-81AE-0005029FC1A7@myrealbox.com>
<200310221239.58302.josh@agliodbs.com>
<18349.1066851898@sss.pgh.pa.us>
<200310221322.26013.josh@agliodbs.com>
<Xns9435D8D7BF744rr8xca@200.46.204.72>
<877k1w6fcv.fsf@stark.dyndns.tv>
<Xns9437E70E5112rr8xca@200.46.204.72>
<9824.1069397116@sss.pgh.pa.us>
<87isld35or.fsf@stark.dyndns.tv>
[sNip]
>> bearing in mind that this index can be used with queries that contain
>> WHERE conditions like "create_date >= some-date-constant". The planner
>> must be able to convince itself that the right-hand side of the WHERE
>> condition is >= the cutoff in the index's predicate condition. Since
>> the planner is not very bright, both items had better be simple DATE
>> constants, or it won't be able to figure it out ...
>
> Note that if you're just doing this to speed up regular queries where
> you have create_date in some small range, then you'll likely not see
> much of an increase. Mainly you'll just save space.
Saving space is not my objective for this particular problem, but of
course it's definitely good news as I strive to optimize from every angle
whenever possible.
What I'm aiming for is to get an increase, no matter how small,
because the volume is expected to be extremely heavy duty (unfortunately I
can't discuss too many details of the project due to agreements I've made
with others), so even if the end result seems insignificant from the
perspective of a single query, the advantages become obvious when the
queries are performed repeatedly simultaneously for wide variety of massive
numbers of clients.
> What can be interesting is to create a partial index like this but over
> a second unrelated column. Something like:
>
> CREATE INDEX my_dec_03_index on my_table (userid)
> WHERE (create_date >= date '2003-11-02');
>
> Then you can do queries like
>
> SELECT * FROM my_table WHERE userid = ? AND create_date >= date
> '2003-11-02'
>
> And it'll be able to efficiently pull out just those records, even if
> there are thousands more records that are older than 2003-11-02.
What a fascinating trick. I could also use dummy data instead of
"userid" (which has obvious functionality as implied by its name), or even
data that the applications can even specify first because they'll be able
to determine things on the client-side that will make index selection more
appropriate.
I haven't checked into this yet, but is there a way to specify which
index PostgreSQL use as a parameter in a SELECT?
> This avoids having to create a two-column index with a low-selectivity
> column like "month".
Thanks, both of you. I've got some re-thinking to do for this project
I'm working on (since it's a low priority project at the moment, I've got
plenty of time to re-work the plan over and over again).
--
Randolf Richardson - rr@8x.ca
Inter-Corporate Computer & Network Services, Inc.
Vancouver, British Columbia, Canada
http://www.8x.ca/
This message originated from within a secure, reliable,
high-performance network ... a Novell NetWare network.
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: rr@8x.ca
Subject: Re: Expressional Indexes
In-Reply-To: <Xns943A725C1BA38rr8xca@200.46.204.72>
* 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