Thread: Composite types questions
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?
What if I define a functional index on col1?
Does the same apply to TABLE-level CHECK conditions?
Thanks in advance.
--
Vincenzo Romano
NotOrAnd Information Technologies
NON QVIETIS MARIBVS NAVTA PERITVS
On Mon, Jan 11, 2010 at 5:14 AM, Vincenzo Romano
<vincenzo.romano@notorand.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
> What if I define a functional index on col1?
sure (if you pull the data the way it is done in the function)
> Does the same apply to TABLE-level CHECK conditions?
probably.
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.
merlin
2010/1/11 Merlin Moncure <mmoncure@gmail.com>:
> On Mon, Jan 11, 2010 at 5:14 AM, Vincenzo Romano
> <vincenzo.romano@notorand.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
On Mon, Jan 11, 2010 at 11:08 AM, Vincenzo Romano > SELECT * FROM master_tab > WHERE col1>='a date':timestamp AND col1<'another date'::timestamp > AND col2=42 AND col3='the answer'; queries of this sort are optimally handled via row constructor for 8.2 onwards: create index col231_idx on master_tab(col2, col3, col1); select * from master_tab where (col2,col3,co1) >= (42, 'the answer', a_date) and (col2,col3,co1) < (42, 'the answer', b_date); one caveat: the planner is lousy in regards to estimating index selectivity in these cases which can cause strange behaviors. regarding constraint_exclusion, I don't think there are any issues...you should test it to be sure. merlin
2010/1/11 Merlin Moncure <mmoncure@gmail.com>: > On Mon, Jan 11, 2010 at 11:08 AM, Vincenzo Romano > SELECT * FROM master_tab >> WHERE col1>='a date':timestamp AND col1<'another date'::timestamp >> AND col2=42 AND col3='the answer'; > > queries of this sort are optimally handled via row constructor for 8.2 onwards: > > create index col231_idx on master_tab(col2, col3, col1); > > select * from master_tab where (col2,col3,co1) >= (42, 'the answer', > a_date) and (col2,col3,co1) < (42, 'the answer', b_date); > > one caveat: the planner is lousy in regards to estimating index > selectivity in these cases which can cause strange behaviors. > > regarding constraint_exclusion, I don't think there are any > issues...you should test it to be sure. > > merlin > Great news for me! Thanks Merlin ... the Wizard! :-) -- Vincenzo Romano NotOrAnd Information Technologies cel. +39 339 8083886 | gtalk. vincenzo.romano@notorand.it fix. +39 0823 454163 | skype. notorand.it fax. +39 02 700506964 | msn. notorand.it NON QVIETIS MARIBVS NAVTA PERITVS