2010/1/11 Merlin Moncure <mmoncure@xxxxxxxxx>:
> On Mon, Jan 11, 2010 at 5:14 AM, Vincenzo Romano
> <vincenzo.romano@xxxxxxxxxxx> wrote:
>> Hi all.
>>
>> It's not clear to me how composite values are used in conditions (WHERE/CHECK).
>> In my case I have something like this:
>>
>> -- begin snippet
>>
>> CREATE TABLE test_tab (
>> col1 timestamp not null,
>> col2 int8 not null,
>> col3 text not null
>> );
>>
>> CREATE INDEX i_test_tab_col1 ON test_tab( col1 );
>>
>> SELECT *
>> FROM test_tab
>> WHERE (date_trunc('week',col1),col2,col3)=('val1'::timestamp,val2,'val3')
>> ;
>> -- end snippet
>>
>> For a number of reasons I cannot split the the WHERE condition in the
>> "usual" AND-list.
>> My questions are:
>> Can the i_test_tab_col1 INDEX be taken into account from the query planner?
> me
> yes
I need to be more precise.
That WHERE condition should fetch all the (relevant) rows whose col1
value falls within a defined week.
I'm not sure whether the query planner understands this without a
specific index. Read below.
>
>> What if I define a functional index on col1?
>
> sure (if you pull the data the way it is done in the function)
I mean an index like this:
CREATE INDEX i_test_tab_col1_trunc ON test_tab( date_trunc( 'week',col1 ) );
or, even better, like this:
CREATE INDEX i_test_tab_misc ON test_tab( date_trunc('week',col1),col2,col3 );
It sounds like to me that Merlin says so.
>
>> Does the same apply to TABLE-level CHECK conditions?
>
> probably.
"Probably" is too fuzzy to me, sorry.
> You are not really asking about composite types. What you
> demonstrated is row constructor syntax...they are similar but not the
> same. For the most part, at least from 8.2 onwards, postgres is
> pretty smart about row constructor and should do what you want with
> minimal fuss.
I mean, if I define a TABLE-level CHECK condition just like this:
...
CHECK ( date_trunc('week',col1),col2,col3)=('val1'::timestamp,val2,'val3') )
....
for a number of inherited tables, and then have a query like this:
SELECT * FROM master_tab
WHERE col1>='a date':timestamp AND col1<'another date'::timestamp
AND col2=42 AND col3='the answer';
will the constraint_exclusion filter skip all those tables in the
inheritance hierarchy whose col1 columns fall outside a needed week?
> merlin
Thanks a lot so far.
--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
