Thread: Partitioning by letter question

Hi, I was wondering if this was possible. I'm trying to partition a 
table, which is straightforward enough thanks to the great 
documentation, but i have a question:

If I partition using something like a product_id for example and have 
check constraints such as (id>=1000 and id<2000) then everything is fine 
and the planner correctly uses the right subset of the tables. However I 
would like to partition by the first letter and using something like 
this substr(word,1,1)='a' is ignored by the planner. From reading the 
docs I understand that complicated check constraints are ignored, but 
this doesn't seem overly complicated.

Am i doing  something wrong or is there another better way to do this

Thanks

John

On Fri, Jan 29, 2010 at 3:24 PM, John Lister
<john.lister-ps@kickstone.com> wrote:
> Hi, I was wondering if this was possible. I'm trying to partition a table,
> which is straightforward enough thanks to the great documentation, but i
> have a question:
>
> If I partition using something like a product_id for example and have check
> constraints such as (id>=1000 and id<2000) then everything is fine and the
> planner correctly uses the right subset of the tables. However I would like
> to partition by the first letter and using something like this
> substr(word,1,1)='a' is ignored by the planner. From reading the docs I
> understand that complicated check constraints are ignored, but this doesn't
> seem overly complicated.
>
> Am i doing  something wrong or is there another better way to do this

Have you tried:

(word >= 'a' and word <'b')

?

><john.lister-ps@kickstone.com> wrote:
>> Hi, I was wondering if this was possible. I'm trying to partition atable,
>> which is straightforward enough thanks to the great documentation, but i
>> have a question:
>>
>> If I partition using something like a product_id for example and have 
>> check
>> constraints such as (id>=1000 and id<2000) then everything is fine and 
>> the
>> planner correctly uses the right subset of the tables. However I would 
>> like
>> to partition by the first letter and using something like this
>> substr(word,1,1)='a' is ignored by the planner. From reading the docs I
>> understand that complicated check constraints are ignored, but this 
>> doesn't
>> seem overly complicated.
>>
>> Am i doing something wrong or is there another better way to do this

>Have you tried:

>(word >= 'a' and word <'b')
Cheers, had my programming head on. One question:
any ideas about what to put for the last in the list
i thought something like  (word>='z' and word<'{') which is based on the 
ascii ordering. - my db is using utf8
I tried to check this by doing
select * from words where word >'zzzz' order by word limit 10;
which returns '.' as the first result (ok not a word, but that is a 
different issue) but if i do
select * from words where word <'.' order by word desc limit 10
I get '/...' as the first result, I would expect 'zzzz', this doesn't seem 
consistent.
I'm obviously missing some inherent sorting behaviour her, but not sure..
Thanks
John

On Sat, Jan 30, 2010 at 7:11 AM, John Lister
<john.lister-ps@kickstone.co.uk> wrote:
>> <john.lister-ps@kickstone.com> wrote:
>
> .> Hi, I was wondering if this was possible. I'm trying to partition a
> table,
> .> which is straightforward enough thanks to the great documentation, but i
>>>
>>> have a question:
>>>
>>> If I partition using something like a product_id for example and have
>>> check
>>> constraints such as (id>=1000 and id<2000) then everything is fine and
>>> the
>>> planner correctly uses the right subset of the tables. However I would
>>> like
>>> to partition by the first letter and using something like this
>>> substr(word,1,1)='a' is ignored by the planner. From reading the docs I
>>> understand that complicated check constraints are ignored, but this
>>> doesn't
>>> seem overly complicated.
>>>
>>> Am i doing something wrong or is there another better way to do this
>
>> Have you tried:
>
>> (word >= 'a' and word <'b')
>
> Cheers, had my programming head on. One question:
>
> any ideas about what to put for the last in the list
>
> i thought something like  (word>='z' and word<'{') which is based on the
> ascii ordering. - my db is using utf8
>
> I tried to check this by doing
>
> select * from words where word >'zzzz' order by word limit 10;
>
> which returns '.' as the first result (ok not a word, but that is a
> different issue) but if i do
>
> select * from words where word <'.' order by word desc limit 10
>
> I get '/...' as the first result, I would expect 'zzzz', this doesn't seem
> consistent.

Yeah, in non C locales, things like . and " " don't count for ordering.

As for the constraints, why not something like:

where word < 'a' or word > 'z'

Or something like that.  Not that I'm not taking upper and lower case
into consideration here.  you might need something like lower(word) <
'a' etc.

Hi all, I was just wondering if any progress has been made on improving 
partitioning, particuarly performance wise. I've found a few documents 
on the web, for example: 
http://wiki.postgresql.org/wiki/Table_partitioning and 
http://wiki.postgresql.org/wiki/Image:Partitioning_Requirements.pdf, 
http://wiki.postgresql.org/wiki/PgCon_2008_Developer_Meeting#Partitioning_Roadmap 
which mention improvements to partitioning, but I can't find any info if 
these have been acted on.

Just curious as things like pushing limits down to the sub queries would 
be a great feature, etc

Cheers

John