Re: [GENERAL] partitioning question

The comparison would be a if then else end if .. about 8 of them 2013-> and a static insert into v's making a dynamic string and using execute, my presumption would be the execute would be expensive verses a INSERT command A On 1 August 2017 at 07:04, Scott Marlowe wrote: > On Sun, Jul 30, 2

Re: [GENERAL] partitioning question

On Sun, Jul 30, 2017 at 7:13 PM, Alex Samad wrote: > How expensive is dynamic over static. I'm looking at storing yearly now, so > I figure if my if then clause has the latest year at the top it should be > very quick. Assuming you're not doing anything particularly crazy it's minimal. But what

Re: [GENERAL] partitioning question

How expensive is dynamic over static. I'm looking at storing yearly now, so I figure if my if then clause has the latest year at the top it should be very quick. On 31 July 2017 at 11:07, Justin Pryzby wrote: > On Mon, Jul 31, 2017 at 10:25:54AM +1000, Alex Samad wrote: > > I note that you l

Re: [GENERAL] partitioning question

On Mon, Jul 31, 2017 at 10:25:54AM +1000, Alex Samad wrote: > I note that you link to P10 and I am currently looking at 9.6. The changes > do look nice for partitioning for p10. Yes sorry, pg10 is beta - avoid using it except for testing purposes. > I will add currently we don't delete anything,

Re: [GENERAL] partitioning question

Hi I note that you link to P10 and I am currently looking at 9.6. The changes do look nice for partitioning for p10. Interesting your suggest that the MM parition isn't that bad. I will add currently we don't delete anything, we will keep adding to it. Also I am thinking my insert trigger

Re: [GENERAL] partitioning question

On Mon, Jul 31, 2017 at 09:15:29AM +1000, Alex Samad wrote: > Hi > > I was about to partition a large (?) approx 3T of data 2B rows into > partition tables but broken up into MM ... > > Now I have been reading about limiting the number of partitions otherwise > it could slow down the parser.

[GENERAL] partitioning question

Hi I was about to partition a large (?) approx 3T of data 2B rows into partition tables but broken up into MM ... Now I have been reading about limiting the number of partitions otherwise it could slow down the parser. My reasoning for limiting to MM was that most of the request would be

Re: [GENERAL] Partitioning

On Tue, 25 Jul 2017 18:21:43 +0530, Krithika Venkatesh wrote: >I have a table that is partitioned on a numeric column (ID). > >Partitioning works when I query the table with no joins. > >SELECT * FROM TABLE A a WHERE ID IN (SELECT ID FROM TABLE B b WHERE >CREATED_TS = CURRENT_TIMESTAMP) > >Partit

Re: [GENERAL] Partitioning (constraint exclusion involving joins)

We understand the constraints exclusion will work only on constant values. But in our case we will never pass a constant value to the partitioning key when we query the partition tables. Will the partition be beneficial in this case. If yes, can you please explain. Thanks On 25-Jul-2017 6:46 PM,

Re: [GENERAL] Partitioning (constraint exclusion involving joins)

On Tue, Jul 25, 2017 at 06:21:43PM +0530, Krithika Venkatesh wrote: > I have a table that is partitioned on a numeric column (ID). > > Partitioning works when I query the table with no joins. > > SELECT * FROM TABLE A a WHERE ID IN (SELECT ID FROM TABLE B b WHERE > CREATED_TS = CURRENT_TIMESTAMP)

[GENERAL] Partitioning

Hi, I have a table that is partitioned on a numeric column (ID). Partitioning works when I query the table with no joins. SELECT * FROM TABLE A a WHERE ID IN (SELECT ID FROM TABLE B b WHERE CREATED_TS = CURRENT_TIMESTAMP) Partitioning doesn't work when I do join. SELECT A.* FROM TABLE A a INNE

Re: [GENERAL] Partitioning and Table Inheritance

Hi Paul, How much of your data is time-series in nature? Put another way, is there a timestamp coupled with the inserted data? Andrew On Fri, May 12, 2017 at 4:38 PM, Ivan E. Panchenko < i.panche...@postgrespro.ru> wrote: > Hi > > > 12.05.2017 23:22, Justin Pryzby пишет: > >> On Mon, May 08, 20

Re: [GENERAL] Partitioning and Table Inheritance

Hi 12.05.2017 23:22, Justin Pryzby пишет: On Mon, May 08, 2017 at 10:12:18AM -0700, Paul A Jungwirth wrote: I'm working on a problem where partitioning seems to be the right approach, but we would need a lot of partitions (say 10k or 100k). Everywhere I read that after ~100 child tables you ex

Re: [GENERAL] Partitioning and Table Inheritance

On Mon, May 08, 2017 at 10:12:18AM -0700, Paul A Jungwirth wrote: > I'm working on a problem where partitioning seems to be the right > approach, but we would need a lot of partitions (say 10k or 100k). > Everywhere I read that after ~100 child tables you experience > problems. I have a few questio

[GENERAL] Partitioning and Table Inheritance

I'm working on a problem where partitioning seems to be the right approach, but we would need a lot of partitions (say 10k or 100k). Everywhere I read that after ~100 child tables you experience problems. I have a few questions about that: 1. Is it true that the only disadvantage to 10k children i

Re: [GENERAL] Partitioning and ORM tools

Here is a working example of trigger based partitioning with a view and 'do instead' that works with ORM tools using the affected rows return (example attached). The key things that make it work are: 1. RETURN NEW; (in the function after inserting into the partition) 2. INSTEAD OF INSERT (in th

Re: [GENERAL] Partitioning and ORM tools

On 03/23/2016 02:48 AM, Chris Travers wrote: On Wed, Mar 23, 2016 at 9:39 AM, Chris Travers mailto:chris.trav...@gmail.com>> wrote: Use a view with a DO INSTEAD trigger. That will allow you to return the tuple properly. On Tue, Mar 22, 2016 at 7:40 PM, CS DBA mailto:cs_...

Re: [GENERAL] Partitioning and ORM tools

On Wed, Mar 23, 2016 at 9:39 AM, Chris Travers wrote: > Use a view with a DO INSTEAD trigger. That will allow you to return the > tuple properly. > > On Tue, Mar 22, 2016 at 7:40 PM, CS DBA > wrote: > >> Hi All; >> >> we setup partitioning for a large table but had to back off because the >> r

Re: [GENERAL] Partitioning and ORM tools

Use a view with a DO INSTEAD trigger. That will allow you to return the tuple properly. On Tue, Mar 22, 2016 at 7:40 PM, CS DBA wrote: > Hi All; > > we setup partitioning for a large table but had to back off because the > return status (i.e: "INSERT 0 1") returns "INSERT 0 0" when inserting i

Re: [GENERAL] Partitioning and ORM tools

On 3/22/2016 2:20 PM, CS DBA wrote: I would think the ORM (as yet undefined) would want to think in terms of the parent table and not know about the physical schema details. Can the client not be written to check only for errors vs checking for non-zero inserts? That was our first suggesti

Re: [GENERAL] Partitioning and ORM tools

> So the ORM is parsing the INSERT return value, correct? > > Would something like this(borrowing from docs example) freak it out?: > > CREATE OR REPLACE FUNCTION measurement_insert_trigger() > RETURNS TRIGGER AS $$ > DECLARE >_ct int; > BEGIN >INSERT INTO measurement_y2016m03 VALUES (NEW

Re: [GENERAL] Partitioning and ORM tools

> So the ORM is parsing the INSERT return value, correct? > > Would something like this(borrowing from docs example) freak it out?: > > CREATE OR REPLACE FUNCTION measurement_insert_trigger() > RETURNS TRIGGER AS $$ > DECLARE >_ct int; > BEGIN >INSERT INTO measurement_y2016m03 VALUES (NEW

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 02:20 PM, CS DBA wrote: On 03/22/2016 03:18 PM, Rob Sargent wrote: On 03/22/2016 03:00 PM, Joshua D. Drake wrote: On 03/22/2016 01:50 PM, CS DBA wrote: Understood, was just wondering if there is a way to cause the child table insert results to be returned to the ORM/Applicat

Re: [GENERAL] Partitioning and ORM tools

On Tue, Mar 22, 2016 at 5:20 PM, CS DBA wrote: > > > On 03/22/2016 03:18 PM, Rob Sargent wrote: > >> >> >> On 03/22/2016 03:00 PM, Joshua D. Drake wrote: >> >>> On 03/22/2016 01:50 PM, CS DBA wrote: >>> >>> Understood, was just wondering if there is a way to cause the child table insert resu

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 03:18 PM, Rob Sargent wrote: On 03/22/2016 03:00 PM, Joshua D. Drake wrote: On 03/22/2016 01:50 PM, CS DBA wrote: Understood, was just wondering if there is a way to cause the child table insert results to be returned to the ORM/Application instead of the master/base table in

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 03:00 PM, Joshua D. Drake wrote: On 03/22/2016 01:50 PM, CS DBA wrote: Understood, was just wondering if there is a way to cause the child table insert results to be returned to the ORM/Application instead of the master/base table insert Insert into the child table directly ba

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 01:50 PM, CS DBA wrote: Understood, was just wondering if there is a way to cause the child table insert results to be returned to the ORM/Application instead of the master/base table insert Insert into the child table directly based on the partition rules. JD -- Command Promp

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 02:43 PM, Joshua D. Drake wrote: On 03/22/2016 01:35 PM, CS DBA wrote: On 03/22/2016 02:23 PM, Joshua D. Drake wrote: On 03/22/2016 11:40 AM, CS DBA wrote: Hi All; we setup partitioning for a large table but had to back off because the return status (i.e: "INSERT 0 1") ret

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 01:35 PM, CS DBA wrote: On 03/22/2016 02:23 PM, Joshua D. Drake wrote: On 03/22/2016 11:40 AM, CS DBA wrote: Hi All; we setup partitioning for a large table but had to back off because the return status (i.e: "INSERT 0 1") returns "INSERT 0 0" when inserting into the partitione

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 02:23 PM, Joshua D. Drake wrote: On 03/22/2016 11:40 AM, CS DBA wrote: Hi All; we setup partitioning for a large table but had to back off because the return status (i.e: "INSERT 0 1") returns "INSERT 0 0" when inserting into the partitioned table which causes the ORM tool to as

Re: [GENERAL] Partitioning and ORM tools

On Tue, Mar 22, 2016 at 4:23 PM, Joshua D. Drake wrote: > On 03/22/2016 11:40 AM, CS DBA wrote: > >> Hi All; >> >> we setup partitioning for a large table but had to back off because the >> return status (i.e: "INSERT 0 1") returns "INSERT 0 0" when inserting >> into the partitioned table which c

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 11:40 AM, CS DBA wrote: Hi All; we setup partitioning for a large table but had to back off because the return status (i.e: "INSERT 0 1") returns "INSERT 0 0" when inserting into the partitioned table which causes the ORM tool to assume the insert inserted 0 rows. Is there a stand

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 01:10 PM, Rob Sargent wrote: On 03/22/2016 12:55 PM, Melvin Davidson wrote: Your problem seems strange as it has never been previously reported for anyone else that has _successfully_ set up partioning. Perhaps is you provide just a little bit more detail we might be able to h

Re: [GENERAL] Partitioning and ORM tools

Melvin Davidson wrote: > Your problem seems strange as it has never been previously reported for > anyone else that has _successfully_ set up partioning. At least as of when I asked a very similar question (http://www.postgresql.org/message-id/flat/547f7e88.7080...@vianet.ca#547f7e88.7080...@viane

Re: [GENERAL] Partitioning and ORM tools

On 03/22/2016 12:55 PM, Melvin Davidson wrote: Your problem seems strange as it has never been previously reported for anyone else that has _successfully_ set up partioning. Perhaps is you provide just a little bit more detail we might be able to help you. Useful and needed information would

Re: [GENERAL] Partitioning and ORM tools

Your problem seems strange as it has never been previously reported for anyone else that has _successfully_ set up partioning. Perhaps is you provide just a little bit more detail we might be able to help you. Useful and needed information would be: 1. Version of PostgreSQL 2. Operating System 3. T

[GENERAL] Partitioning and ORM tools

Hi All; we setup partitioning for a large table but had to back off because the return status (i.e: "INSERT 0 1") returns "INSERT 0 0" when inserting into the partitioned table which causes the ORM tool to assume the insert inserted 0 rows. Is there a standard / best practices work around fo

Re: [GENERAL] Partitioning table by multiple columns

On 10/28/15 6:57 AM, mephysto wrote: Hi there, I need to partition a table by value of two columns: id_1 and id_2. They are both bigint. Anyone have experience in this? Anyone know how can I do this partitioning? It will work just like regular table partitioning. You just need to account for

[GENERAL] Partitioning table by multiple columns

Hi there, I need to partition a table by value of two columns: id_1 and id_2. They are both bigint. Anyone have experience in this? Anyone know how can I do this partitioning? Thanks in advance. Meph -- View this message in context: http://postgresql.nabble.com/Partitioning-table-by-multipl

Re: [GENERAL] Partitioning and constraint exclusion

> > > > ​I am not sure but am doubting it is intelligent enough to recognize the > functional expression even if all of the values are present. "simple > equality" ( > http://www.postgresql.org/docs/9.4/interactive/ddl-partitioning.html) > this is not. > Looks like the tables with about 100+ valu

Re: [GENERAL] Partitioning and constraint exclusion

On Mon, Sep 7, 2015 at 7:12 PM, Melvin Davidson wrote: > First, what is the PostgresSQL version ?? > 9.3.6 > > Next, in postgresql.conf, what is the value of constraint_exclusion ? > partition In response to the other possible issues pointed out - the planner is indeed *skipping the rest

Re: [GENERAL] Partitioning and constraint exclusion

On Mon, Sep 7, 2015 at 4:48 PM, David G. Johnston < david.g.johns...@gmail.com> wrote: > IIRC ​The planner doesn't understand​ > > ​overlaps so having a definition of: > > IN (1,2,3,4,5); or nearly equivalently = ANY(ARRAY[1,2,3,4,5]))​ > > and a request for: > > IN (1,3,5) / = ANY(ARRAY[1,3,5]) i

Re: [GENERAL] Partitioning and constraint exclusion

On Mon, Sep 7, 2015 at 8:55 AM, Jayadevan M wrote: > Hello , > > I have a parent table and 6 child tables (partitions). The child tables > have check constraints defined in the form > CHECK (myuid in (123,456,..)). > myuid is bigint, the constraints for the 6 child tables are definitely > mutuall

Re: [GENERAL] Partitioning and constraint exclusion

First, what is the PostgresSQL version ?? Next, in postgresql.conf, what is the value of constraint_exclusion ? On Mon, Sep 7, 2015 at 8:55 AM, Jayadevan M wrote: > Hello , > > I have a parent table and 6 child tables (partitions). The child tables > have check constraints defined in the fo

[GENERAL] Partitioning and constraint exclusion

Hello , I have a parent table and 6 child tables (partitions). The child tables have check constraints defined in the form CHECK (myuid in (123,456,..)). myuid is bigint, the constraints for the 6 child tables are definitely mutually exclusive. The number of values in the list ranges from 2-10 for

Re: [GENERAL] Partitioning and performance

Ravi Krishna writes: > So cost wise they both look same, still when i run the sql in a loop > in large numbers, it takes rougly 1.8 to 2 times more than non > partitioned table. If you're testing cases that only involve fetching a single row, the discrepancy could well be down to extra planning

Re: [GENERAL] Partitioning and performance

Ravi Krishna writes: > Is there a rule of thumb as to at what size does the partitioning > start performing better than non partitioned table. Personally I'd not worry about partitioning until I had a table approaching maybe a billion (1e9) rows. You could argue that an order of magnitude either

Re: [GENERAL] Partitioning and performance

On Thu, May 28, 2015 at 12:50 PM, Tom Lane wrote: > Sure, because you don't have a constraint forbidding the parent from > having a matching row, no? As suggested by you, I included a bogus condition in the parent table which will prevent any row addition in the parent table and made the constra

Re: [GENERAL] Partitioning and performance

> By and large, though, this doesn't really matter, since an empty > parent table won't cost anything much to scan. If it's significant > relative to the child table access time then you probably didn't > need partitioning in the first place. Is there a rule of thumb as to at what size does the p

Re: [GENERAL] Partitioning and performance

On Thu, May 28, 2015 at 12:42 PM, Melvin Davidson wrote: > > Generally, when you partition, data should only be in child tables, and the > parent table should be empty, otherwise you defeat the purpose of > parttioning.` yes of course the parent table is empty. The trigger on insert is redirect

Re: [GENERAL] Partitioning and performance

Ravi Krishna writes: > Perhaps I was not clear. The planner is excluding partitions which can > not contain the rows looked up in the WHERE clause. However it is > still scanning the parent table. Sure, because you don't have a constraint forbidding the parent from having a matching row, no? In

Re: [GENERAL] Partitioning and performance

Am 28. Mai 2015 18:25:42 MESZ, schrieb Ravi Krishna : >> Have you set up constraints on the partitions? The planner needs to >know >> what is in the child tables so it can avoid scanning them. > >Yes. each child table is defined as follows > >CREATE TABLE TSTESTING.ACCOUNT_PART1 > > ( CHECK (ACCO

Re: [GENERAL] Partitioning and performance

Generally, when you partition, data should only be in child tables, and the parent table should be empty, otherwise you defeat the purpose of parttioning.` On Thu, May 28, 2015 at 12:25 PM, Ravi Krishna wrote: > > Have you set up constraints on the partitions? The planner needs to know > > what

Re: [GENERAL] Partitioning and performance

Am 28. Mai 2015 17:15:22 MESZ, schrieb Ravi Krishna : >I am testing partitioning of a large table. I am INHERITING child >tables. >It is using a range >partitioning based on a sequence col, which also acts as the primary >key. For inserts I am using a trigger which will redirect insert to >the ri

Re: [GENERAL] Partitioning and performance

> Have you set up constraints on the partitions? The planner needs to know > what is in the child tables so it can avoid scanning them. Yes. each child table is defined as follows CREATE TABLE TSTESTING.ACCOUNT_PART1 ( CHECK (ACCOUNT_ROW_INST BETWEEN 1001 and 271660)) INHERITS (TSTESTING.ACCO

[GENERAL] Partitioning and performance

I am testing partitioning of a large table. I am INHERITING child tables. It is using a range partitioning based on a sequence col, which also acts as the primary key. For inserts I am using a trigger which will redirect insert to the right table based on the value of the primary key. Based on my

Re: [GENERAL] Partitioning and constraint exclusion

Sam, * Samuel Smith (pg...@net153.net) wrote: > Does anyone know if there is a wishlist item for improving this in > postgresql or is this as good as it gets for now? It's absolutely on the todo list for PG to support declarative partitioning and handle these cases better. There has been a good

Re: [GENERAL] Partitioning and constraint exclusion

On 03/04/2015 12:11 AM, David G Johnston wrote: In short - since the planner determines exclusion constraints and the executor, which strictly follows the planner in the query execution process, would be the one to determine what the value of your date is - there is no way for a single query to

Re: [GENERAL] Partitioning with the index on the master table.

On 3/4/15 9:25 AM, Semyon Reyfman wrote: I have a list-based partition table and I want to prevent scanning of all children tables in a JOIN, which is using the partition column. I know that CHECK constraints are not used in this case Why do you say that? If properly written and with constrai

[GENERAL] Partitioning with the index on the master table.

I have a list-based partition table and I want to prevent scanning of all children tables in a JOIN, which is using the partition column. I know that CHECK constraints are not used in this case so I hope that creating an index on the partition column would help. But I am not sure if I should cre

Re: [GENERAL] Partitioning and constraint exclusion

Samuel, * Samuel Smith (pg...@net153.net) wrote: > I noticed that I could get very nice partition elimination using > constant values in the where clause. > > Ex: > select * from where between '2015-01-01' > and '2015-02-15' > > However, I could not get any partition elimination for queries th

Re: [GENERAL] Partitioning and constraint exclusion

Samuel Smith wrote > I noticed that I could get very nice partition elimination using > constant values in the where clause. > > Ex: > select * from > where > > between '2015-01-01' and > '2015-02-15' > > However, I could not get any partition elimination for queries that did > not have

[GENERAL] Partitioning and constraint exclusion

Howdy, I spent a majority of today playing around with pg_partman (awesome tool btw!). I am mainly using the time-static method with an interval of one month. I wanted to see what performance improvements I could get with some common queries that are used by our analytics team. A lot of these

Re: [GENERAL] partitioning query planner almost always scans all tables

Thank you for your input. When doing an "ORDER BY measurement_time DESC" I had hoped for the query planner to firstly query the most recent "child" table, i.e. the table holding current month's data, and then move-on to the oldest table, since it knows the partition is based on measurement_time. I

Re: [GENERAL] partitioning query planner almost always scans all tables

Spiros Ioannou writes: > It is ORDER BY measurement_time, not measurement_id, and measurement_time > is used to create the partition. So the planner should know the correct > order, but instead it seems to query tables in the wrong order. The planner does not know that, and even if it attempted t

Re: [GENERAL] partitioning query planner almost always scans all tables

> > > > EXPLAIN ANALYZE SELECT * FROM measurement_events WHERE > > measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' ORDER BY > > measurement_time DESC LIMIT 1; > > > > This seems to fail, scanning all tables. Do you think this can be > improved > > at all ? The query plan of the above

Re: [GENERAL] partitioning query planner almost always scans all tables

Hi, > @Kyotaro HORIGUCHI > thanks for your reply and time Kyotaro, Not at all. > Using the following query > EXPLAIN ANALYZE SELECT * FROM measurement_events WHERE > measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' AND > measurement_time >= '2015-01-01 00:00:00+0' LIMIT 1; > > pro

Re: [GENERAL] partitioning query planner almost always scans all tables

@Rob Sargent: sorry Rob, not sure what you are asking. @Kyotaro HORIGUCHI thanks for your reply and time Kyotaro, Using the following query EXPLAIN ANALYZE SELECT * FROM measurement_events WHERE measurement_source_id='df86917e-8df0-11e1-8f8f-525400e76ceb' AND measurement_time >= '2015-01-01 00:0

Re: [GENERAL] partitioning query planner almost always scans all tables

Hello, Your constraint column is of 'timestamp with time zone' and the query uses 'date'. The comparison between them is an operator based on a static, non-immutable function so constraint exclusion doesn't work. SELECT o.oprname, o.oprcode, p.provolatile FROM pg_operator o join pg_proc p on (p.

Re: [GENERAL] Partitioning

Hi, 19 Jan 2015 06:58:21 -0500, François Beausoleil wrote in > > > Le 2015-01-18 à 20:58, James Sewell a écrit : > > > > Hello, > > > > I am using partitioning with around 100 sub-tables. Each sub-table is > > around 11GB and partitioned on the 'id' column. I have an index on the id > >

Re: [GENERAL] partitioning query planner almost always scans all tables

I don't understand having both UUID and time stamp in your PK? The first is by defn. unique and the second might be. Sent from my iPhone > On Jan 19, 2015, at 6:12 AM, Spiros Ioannou wrote: > > Hello group, > we have a timeseries table, and we tried to partition it by month (with > pg_partma

[GENERAL] partitioning query planner almost always scans all tables

Hello group, we have a timeseries table, and we tried to partition it by month (with pg_partman). It seems the query planner always reads all tables regardless of WHERE, except when WHERE is equality. *the parent table:* ifms_db=# \dS measurement_events Table "public.measurement_eve

Re: [GENERAL] Partitioning

> Le 2015-01-18 à 20:58, James Sewell a écrit : > > Hello, > > I am using partitioning with around 100 sub-tables. Each sub-table is around > 11GB and partitioned on the 'id' column. I have an index on the id column on > each sub-table. > > Is it possible to get a query like the following wo

Re: [GENERAL] Partitioning

Sorry, > Yeah, scanning children reading highest/lowest values for each of them would make it faster than the first single query. Mmm..no. It has nothing different from querieng on the parent table. Please Ignore the silly thing. -- Kyotaro Horiguchi

Re: [GENERAL] Partitioning

Hi, At Mon, 19 Jan 2015 00:14:55 -0800, John R Pierce wrote in <54bcbcff.5040...@hogranch.com> > On 1/18/2015 11:59 PM, James Sewell wrote: > > > > Actually this brings up another question, if I have an unique integer > > index of 2.gb what percentage would I expect to read for > >

Re: [GENERAL] Partitioning

On 1/18/2015 11:59 PM, James Sewell wrote: Actually this brings up another question, if I have an unique integer index of 2.gb what percentage would I expect to read for a value that was higher or lower than all index values? a couple 8k blocks. its a b-tree. -- john r pierce

Re: [GENERAL] Partitioning

Yeah definitely not staying resident once read, although the machine does gave 256GB of memory so some will persist in the OS cache. Actually this brings up another question, if I have an unique integer index of 2.gb what percentage would I expect to read for a value that was higher or lower than

Re: [GENERAL] Partitioning

On 1/18/2015 11:13 PM, James Sewell wrote: Each index is about 2.5GB, I suspect I am trying to read a these into memory in entirety. an 11GB table with a (presumably integer) primary key requires an 2.5GB index ? 100 of these would need 250GB of shared_buffers to stay resident, not lik

Re: [GENERAL] Partitioning

Hello, Thanks for the reply. I can write queries which get index scans, but they are still slow. Each index is about 2.5GB, I suspect I am trying to read a these into memory in entirety. Perhaps there is no way to tune this? Cheers, james On Monday, 19 January 2015, Kyotaro HORIGUCHI < horig

Re: [GENERAL] Partitioning

Hello, an 2015 14:13:37 +1100, James Sewell wrote in > Sadly not ... I still hit all the tables. | 5.9.4. Partitioning and Constraint Exclusion http://www.postgresql.org/docs/9.4/static/ddl-partitioning.html Constraint exclusion is a mechanism to omit tables that are known to have no hit by

Re: [GENERAL] Partitioning

Sadly not ... I still hit all the tables. Cheers, James Sewell, PostgreSQL Team Lead / Solutions Architect __ Level 2, 50 Queen St, Melbourne VIC 3000 *P *(+61) 3 8370 8000 *W* www.lisasoft.com *F *(+61) 3 8370 8099 On Mon, Jan 19, 2015 at 1:54 PM, J

Re: [GENERAL] Partitioning

On 1/18/2015 5:58 PM, James Sewell wrote: WITH idlist as (SELECT id from othertable) SELECT id from mastertable WHERE id = idlist.id ); select mt.id, ... from mastertable mt join othertable ot on mt.id=ot.id; might optimize better. -- john r pierce

[GENERAL] Partitioning

Hello, I am using partitioning with around 100 sub-tables. Each sub-table is around 11GB and partitioned on the 'id' column. I have an index on the id column on each sub-table. Is it possible to get a query like the following working using constraint exclusion, or am I doomed to do index/sequenti

Re: [GENERAL] Partitioning of a dependent table not based on date

On 01/12/2014, at 19:26, Andy Colson wrote: > On 12/1/2014 11:14 AM, Herouth Maoz wrote: >> I am currently in the process of creating a huge archive database that >> contains data from all of our systems, going back for almost a decade. >> >> Most of the tables fall into one of two categories: >

[GENERAL] Partitioning such that key field of inherited tables no longer retains any selectivity

The subject line may not actually describe what I want to illustrate… Basically, let’s say we have a nicely partitioned data-set. Performance is a net win and I’m happy with it. The partitioning scheme is equality based, rather than range based. That is, each partition contains a subset of the da

Re: [GENERAL] Partitioning and triggers

Em 17/11/2013 18:45, Jeff Janes escreveu: On Sun, Nov 17, 2013 at 8:46 AM, Edson Richter mailto:edsonrich...@hotmail.com>> wrote: Dear community, In documentation, when partitioning tables, it is said that "Optionally, define a trigger or rule to redirect data inserted into the

Re: [GENERAL] Partitioning and triggers

On Sun, Nov 17, 2013 at 8:46 AM, Edson Richter wrote: > Dear community, > > In documentation, when partitioning tables, it is said that "Optionally, > define a trigger or rule to redirect data inserted into the master table to > the appropriate partition." > Is the trigger creation optional? I mea

[GENERAL] Partitioning and triggers

Dear community, In documentation, when partitioning tables, it is said that "Optionally, define a trigger or rule to redirect data inserted into the master table to the appropriate partition." Is the trigger creation optional? I mean, partitioning will not work as expected if we don't have the

Re: [GENERAL] partitioning for speed, but query planner ignores

On Wed, Oct 2, 2013 at 9:01 AM, David Rysdam wrote: > I had some vague notion of tables "doing work" but really if it can load > one > partition into RAM and get most of my hits from there, it'd be a big > win. The same concept applies to the frequently-used indexes on that partition.

Re: [GENERAL] partitioning for speed, but query planner ignores

David Rysdam wrote: > Would the planner be smart enough to figure out ranges without me > having to "hint" my queries? Yes, it handles ranges well. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company -- Sent via pgsql-general mailing list (pgsql-general@postg

Re: [GENERAL] partitioning for speed, but query planner ignores

On Wed, 2 Oct 2013 11:19:58 -0400, Kevin Grittner wrote: > David Rysdam wrote: > > > We have a by-our-standards large table (about 40e6 rows). Since it is > > the bottleneck in some places, I thought I'd experiment with > > partitioning. > > In my personal experience I have gone into hundreds o

Re: [GENERAL] partitioning for speed, but query planner ignores

David Rysdam wrote: > We have a by-our-standards large table (about 40e6 rows). Since it is > the bottleneck in some places, I thought I'd experiment with > partitioning. In my personal experience I have gone into hundreds of millions of rows with good performance without partitioning.  It's all

Re: [GENERAL] partitioning for speed, but query planner ignores

On Wed, 2 Oct 2013 09:12:02 -0400, Bill Moran wrote: > Last I looked, the partitioning mechanism isn't _quite_ as smart as could > be desired. For example: > SELECT * FROM table WHERE objnum = 5; -- will not take advantage of partition > You have to give the planner a little more hint as to the f

Re: [GENERAL] partitioning for speed, but query planner ignores

On Wed, 2 Oct 2013 08:34:44 -0400 David Rysdam wrote: > We have a by-our-standards large table (about 40e6 rows). Since it is > the bottleneck in some places, I thought I'd experiment with > partitioning. I'm following the instructions here: > > http://www.postgresql.org/docs/current/static/

Re: [GENERAL] partitioning for speed, but query planner ignores

On Wed, 2 Oct 2013 08:34:44 -0400, David Rysdam wrote: > However, when I run an explain or an explain analyze, I still seeing it > checking both partitions. Is this because the query planner doesn't want > to do a mod? Should I go with simple ranges, even though this adds a > maintenance task? I

[GENERAL] partitioning for speed, but query planner ignores

We have a by-our-standards large table (about 40e6 rows). Since it is the bottleneck in some places, I thought I'd experiment with partitioning. I'm following the instructions here: http://www.postgresql.org/docs/current/static/ddl-partitioning.html The table holds data about certain objects,

Re: [GENERAL] Partitioning table with billion row

1. You have to remove foreign key reference from table searchcache. 2. take backup of data from searchcache. 3. create partition of table product 4. add constraints on table searchcache.(if necessary delete and create searchcache after taking backup.) - Thanks and Regards, Sachin Kotwal

Re: [GENERAL] Partitioning V schema

On Fri, Sep 20, 2013 at 4:38 AM, Julian wrote: > However, I tend to go with partitions when required to be generated on > demand dynamically and automatically (which probably isn't the case > here). SCHEMAs have other uses, provide a level of security (GRANT) and > useful in design when partitioni

Re: [GENERAL] Partitioning V schema

On 21/09/13 02:51, Gregory Haase wrote: I would look towards how PostGis handles the Tiger census data for guidance. It's a similar, massive data set. Greg Haase I'm not sure why it wouldn't handle it fine? The question is at what point would third party "imported" datasets, required for l

Re: [GENERAL] Partitioning V schema

I would look towards how PostGis handles the Tiger census data for guidance. It's a similar, massive data set. Greg Haase On Sep 20, 2013 9:47 AM, "Jeff Janes" wrote: > On Thu, Sep 19, 2013 at 12:02 AM, Dave Potts wrote: > >> Hi List >> >> I am looking for some general advice about the best was

  1   2   3   >