Обсуждение: 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