At 05:58 PM 6/1/00 -0400, you wrote:
Jerry Lynde <jlynde@diligence.com> writes:
>>>> They are all indexed, the DOB index is actually DOBYear DOBDay and
>>>> DOBMonth and all 5 fields are indexed
>>
>> Do you have 5 indexes or do you have an index that spans more than one
>> field?
> Sorry for being less than explicit. There are 5 separate indices, one per
> field.
So your query is really something more like
... WHERE firstname = 'joe' AND lastname = 'blow' AND
DOByear = 1999 AND DOBmonth = 1 AND DOBday = 1
?
yes
The problem here is that only one index can be used in any individual
scan. If I were the optimizer I'd probably figure that lastname is
going to be the most selective of the five available choices, too.
and it did, and that's ok
I'd suggest storing the DOB as *one* field of type 'date'. You can
pull out the subparts for display with date_part() when you need to,
but for searches you'll be a lot better off with
WHERE DOB = '1999-01-01'
regards, tom lane
Thanks for the tip. I might indeed take that approach in the future,
however that's not really the problem I'm trying to tackle right now.
Indexing by Last Name is fine with me, currently. What's not working for me
is the part where the dual pentium 500 machine with 256MB RAM goes into
deep thought indefinitely for one simple hard-coded query.
I used to think that the problem was due to the phpdb module that I was
invoking, since the behavior exhibited itself consistently doing the
aforementioned query with the phpdb module. Using nothing but straight php
I have been able to make the query run smoothly.
The reason I no longer believe the problem was tied to phpdb is that the
behavior with the processors (all processor time devoted to user processes)
happened when I was not making use of phpdb anymore. In fact I wasn't even
making queries at the time, so it may not be tied to postgres at all, but I
suspect it might, since the problem happens at random currently, but was
consistent and predictable with the phpdb-driven postgres query.
