Re: Composite types questions

2010/1/11 Merlin Moncure <mmoncure(at)gmail(dot)com>:
> On Mon, Jan 11, 2010 at 5:14 AM, Vincenzo Romano
> <vincenzo(dot)romano(at)notorand(dot)it> 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