pg.ddx.io  pgsql-general@postgresql.org mailing list archive  
help / color / mirror / Atom feed
partitioning
32+ messages / 16 participants
[nested] [flat]

* partitioning
@ 2003-10-24 22:00 Dennis Gearon <gearond@fireserve.net>
  2003-10-24 22:35 ` Re: partitioning scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 1 reply; 32+ messages in thread

From: Dennis Gearon @ 2003-10-24 22:00 UTC (permalink / raw)
  To: pgsql-general

On small productions systems, single disk, etc, how do you guys like to 
partition the system? Postgres dirs also?




^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: partitioning
  2003-10-24 22:00 partitioning Dennis Gearon <gearond@fireserve.net>
@ 2003-10-24 22:35 ` scott.marlowe <scott.marlowe@ihs.com>
  0 siblings, 0 replies; 32+ messages in thread

From: scott.marlowe @ 2003-10-24 22:35 UTC (permalink / raw)
  To: Dennis Gearon <gearond@fireserve.net>; +Cc: pgsql-general

On Fri, 24 Oct 2003, Dennis Gearon wrote:

> On small productions systems, single disk, etc, how do you guys like to 
> partition the system? Postgres dirs also?

I'll assume you're talking Linux, but other flavors of unix would be 
similar:

On small systems, I generally put a pair IDE drives in and mirror 
everything from one to the other (they're cheap, and mirror sets have 
saved my butt on many occasions.)

/ <- 250 Meg (including /boot
/usr <- 1 to 4 gigs
/tmp <- 1 gig
/var <- 1 gig
/db  <- where pgsql data lives, as big as it needs to be.
/www <- where apache data lives, as big as it needs to be.
/usr/local OR /opt depending on OS <- ~ 1 to 4 gig

The main reason for doing this kind of thing is so that if some process 
goes nuts and fills up your logs (/var) temp directory (/tmp) or database 
partition (/db) it won't bring everything else to its knees, just the area 
it affected.  




^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Partitioning...
@ 2006-06-12 22:02 Milen Kulev <makulev@gmx.net>
  2006-06-12 22:17 ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 32+ messages in thread

From: Milen Kulev @ 2006-06-12 22:02 UTC (permalink / raw)
  To: pgsql-general

Hi listers,
I am trying to learn PG partioning (constaraint exclustion).
I have created pretty simple table (all the code is below), but when I try to populate
The table with data, the RULE system is not working as expected (e.g. as I have expected).
The code:

-------------------------------------------
CREATE TABLE part (
    id1    int not null,
    id2    int not null,
    filler varchar(200)
    );
    
create table part_id1_0_10  ( CHECK ( id1>= 0 and  id1<=10)  ) INHERITS  (part);
create table part_id1_11_20 ( CHECK ( id1>=11 and  id1<=20)  ) INHERITS  (part);

CREATE INDEX idx_part_id1_0_10 ON part_id1_0_10(id1);
CREATE INDEX idx_part_id1_11_20 ON part_id1_11_20(id1);


CREATE RULE part_id1_0_10_insert AS ON INSERT TO part 
WHERE ( id1>= 0 and id1<=10 )   
DO INSTEAD INSERT INTO part_id1_0_10 VALUES ( NEW.id1, NEW.id2, NEW.filler);

CREATE RULE part_id1_11_20_insert AS ON INSERT TO part 
WHERE ( id1>=11 and  id1<=20 ) 
DO INSTEAD INSERT INTO part_id1_11_20 VALUES ( NEW.id1, NEW.id2, NEW.filler);


analyze part_id1_0_10 ;
analyze part_id1_11_20 ;


CREATE VIEW part_all AS
select * from  part_id1_0_10
UNION ALL 
select * from  part_id1_11_20
;


postgres=# \d+  part
                    Table "public.part"
 Column |          Type          | Modifiers | Description
--------+------------------------+-----------+-------------
 id1    | integer                | not null  |
 id2    | integer                | not null  |
 filler | character varying(200) |           |
Rules:
    part_id1_0_10_insert AS
    ON INSERT TO part
   WHERE new.id1 >= 0 AND new.id1 <= 10 DO INSTEAD  INSERT INTO part_id1_0_10 (id1, id2, filler)
  VALUES (new.id1, new.id2, new.filler)
    part_id1_11_20_insert AS
    ON INSERT TO part
   WHERE new.id1 >= 11 AND new.id1 <= 20 DO INSTEAD  INSERT INTO part_id1_11_20 (id1, id2, filler)
  VALUES (new.id1, new.id2, new.filler)
Has OIDs: no


-------------------------------------------

When I try :
 insert into  part(id1, id2, filler) 
 select  
 round(  (random()*10)::bigint,0) as id1,
 round( (random()*20)::bigint,0) as id2,
 'TTTTTTTTTTTESTTTTTZZZZZZZZZZZZZZZZZZZ'
 from  generate_series(0,100000);


All the data is redirected to part_id1_0_10 (as expected).
But When I issue:
 insert into  part(id1, id2, filler) 
 select  
 round(  (random()*20)::bigint,0) as id1, <---!!! Note that both partitions should be populated!
 round( (random()*20)::bigint,0) as id2,
 'TTTTTTTTTTTESTTTTTZZZZZZZZZZZZZZZZZZZ'
 from  generate_series(0,100000);

I am getting :

 ERROR:  new row for relation "part_id1_0_10" violates check constraint "part_id1_0_10_id1_check"

How to  fix the problem ? I thought that the rules were enough to redirect to records to 
The right partions. Should I use triggers instead. The documentation is saying that can use either
Rules OR triggers:
http://www.enterprisedb.com/documentation/ddl-partitioning.html

Point 5 in 4.10.2. Implementing Partitioning.

Any  suggestions ? 

Many thanks in advance 
Milen 




^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning...
  2006-06-12 22:02 Partitioning... Milen Kulev <makulev@gmx.net>
@ 2006-06-12 22:17 ` Tom Lane <tgl@sss.pgh.pa.us>
  2006-06-12 22:25   ` Re: Partitioning... Milen Kulev <makulev@gmx.net>
  2006-06-12 22:34   ` Re: Partitioning... Milen Kulev <makulev@gmx.net>
  0 siblings, 2 replies; 32+ messages in thread

From: Tom Lane @ 2006-06-12 22:17 UTC (permalink / raw)
  To: Milen Kulev <makulev@gmx.net>; +Cc: pgsql-general

"Milen Kulev" <makulev@gmx.net> writes:
> But When I issue:
>  insert into  part(id1, id2, filler) 
>  select  
>  round(  (random()*20)::bigint,0) as id1, <---!!! Note that both partitions should be populated!
>  round( (random()*20)::bigint,0) as id2,
>  'TTTTTTTTTTTESTTTTTZZZZZZZZZZZZZZZZZZZ'
>  from  generate_series(0,100000);

>  ERROR:  new row for relation "part_id1_0_10" violates check constraint "part_id1_0_10_id1_check"

Don't use random() in your test case.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning...
  2006-06-12 22:02 Partitioning... Milen Kulev <makulev@gmx.net>
  2006-06-12 22:17 ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-06-12 22:25   ` Milen Kulev <makulev@gmx.net>
  2006-06-12 22:58     ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 32+ messages in thread

From: Milen Kulev @ 2006-06-12 22:25 UTC (permalink / raw)
  To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-general

Thanks for the prompt reply Tom,
What is wrong with random() ? 

The following snipped is working ...

 insert into  part(id1, id2, filler) 
 select  
  11 + round( (random()*9)::bigint,0)  as id1,  --- 11-20 range for id1 , as of  definition
 round( (random()*20)::bigint,0) as id2,
 'TTTTTTTTTTTESTTTTTZZZZZZZZZZZZZZZZZZZ'
 from  generate_series(0,100000);

Regards. Milen 

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Tuesday, June 13, 2006 12:18 AM
To: Milen Kulev
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Partitioning... 


"Milen Kulev" <makulev@gmx.net> writes:
> But When I issue:
>  insert into  part(id1, id2, filler)
>  select  
>  round(  (random()*20)::bigint,0) as id1, <---!!! Note that both partitions should be populated!
>  round( (random()*20)::bigint,0) as id2,
>  'TTTTTTTTTTTESTTTTTZZZZZZZZZZZZZZZZZZZ'
>  from  generate_series(0,100000);

>  ERROR:  new row for relation "part_id1_0_10" violates check 
> constraint "part_id1_0_10_id1_check"

Don't use random() in your test case.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly




^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning...
  2006-06-12 22:02 Partitioning... Milen Kulev <makulev@gmx.net>
  2006-06-12 22:17 ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
  2006-06-12 22:25   ` Re: Partitioning... Milen Kulev <makulev@gmx.net>
@ 2006-06-12 22:58     ` Tom Lane <tgl@sss.pgh.pa.us>
  2006-06-12 23:04       ` Re: Partitioning... Milen Kulev <makulev@gmx.net>
  2006-06-13 23:09       ` Re: Partitioning... Jim C. Nasby <jnasby@pervasive.com>
  0 siblings, 2 replies; 32+ messages in thread

From: Tom Lane @ 2006-06-12 22:58 UTC (permalink / raw)
  To: Milen Kulev <makulev@gmx.net>; +Cc: pgsql-general

"Milen Kulev" <makulev@gmx.net> writes:
> What is wrong with random() ? 

Not guaranteed to be stable across the multiple evaluations that the
rule will perform ... remember a rule is a macro and has the usual
multiple-evaluation gotchas in the face of volatile arguments.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning...
  2006-06-12 22:02 Partitioning... Milen Kulev <makulev@gmx.net>
  2006-06-12 22:17 ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
  2006-06-12 22:25   ` Re: Partitioning... Milen Kulev <makulev@gmx.net>
  2006-06-12 22:58     ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-06-12 23:04       ` Milen Kulev <makulev@gmx.net>
  1 sibling, 0 replies; 32+ messages in thread

From: Milen Kulev @ 2006-06-12 23:04 UTC (permalink / raw)
  To: 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-general

Aha !
Obviosly that is the reason for working sometimes properly ans sometimes not ...
Thanks !

Regards. Milen

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Tuesday, June 13, 2006 12:58 AM
To: Milen Kulev
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Partitioning... 


"Milen Kulev" <makulev@gmx.net> writes:
> What is wrong with random() ?

Not guaranteed to be stable across the multiple evaluations that the rule will perform ... remember a rule is a macro
and has the usual multiple-evaluation gotchas in the face of volatile arguments.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

               http://archives.postgresql.org




^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning...
  2006-06-12 22:02 Partitioning... Milen Kulev <makulev@gmx.net>
  2006-06-12 22:17 ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
  2006-06-12 22:25   ` Re: Partitioning... Milen Kulev <makulev@gmx.net>
  2006-06-12 22:58     ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-06-13 23:09       ` Jim C. Nasby <jnasby@pervasive.com>
  2006-06-14 11:47         ` Re: Partitioning... Milen Kulev <makulev@gmx.net>
  1 sibling, 1 reply; 32+ messages in thread

From: Jim C. Nasby @ 2006-06-13 23:09 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Milen Kulev <makulev@gmx.net>; pgsql-general

On Mon, Jun 12, 2006 at 06:58:06PM -0400, Tom Lane wrote:
> "Milen Kulev" <makulev@gmx.net> writes:
> > What is wrong with random() ? 
> 
> Not guaranteed to be stable across the multiple evaluations that the
> rule will perform ... remember a rule is a macro and has the usual
> multiple-evaluation gotchas in the face of volatile arguments.

I believe a safe alternative would be...

INSERT INTO ... SELECT * FROM
    (SELECT random()*20 FROM ...)
;

You might need to add an ORDER BY to the subquery to ensure PostgreSQL
doesn't pull it into the main query.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461



^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning...
  2006-06-12 22:02 Partitioning... Milen Kulev <makulev@gmx.net>
  2006-06-12 22:17 ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
  2006-06-12 22:25   ` Re: Partitioning... Milen Kulev <makulev@gmx.net>
  2006-06-12 22:58     ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
  2006-06-13 23:09       ` Re: Partitioning... Jim C. Nasby <jnasby@pervasive.com>
@ 2006-06-14 11:47         ` Milen Kulev <makulev@gmx.net>
  2006-06-14 13:56           ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 32+ messages in thread

From: Milen Kulev @ 2006-06-14 11:47 UTC (permalink / raw)
  To: 'Jim C. Nasby' <jnasby@pervasive.com>; 'Tom Lane' <tgl@sss.pgh.pa.us>; +Cc: pgsql-general

Aha !!!
Thanks Jim, the "trick" worked for me !

Regards. Milen 

-----Original Message-----
From: Jim C. Nasby [mailto:jnasby@pervasive.com] 
Sent: Wednesday, June 14, 2006 1:09 AM
To: Tom Lane
Cc: Milen Kulev; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Partitioning...


On Mon, Jun 12, 2006 at 06:58:06PM -0400, Tom Lane wrote:
> "Milen Kulev" <makulev@gmx.net> writes:
> > What is wrong with random() ?
> 
> Not guaranteed to be stable across the multiple evaluations that the 
> rule will perform ... remember a rule is a macro and has the usual 
> multiple-evaluation gotchas in the face of volatile arguments.

I believe a safe alternative would be...

INSERT INTO ... SELECT * FROM
    (SELECT random()*20 FROM ...)
;

You might need to add an ORDER BY to the subquery to ensure PostgreSQL doesn't pull it into the main query.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461




^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning...
  2006-06-12 22:02 Partitioning... Milen Kulev <makulev@gmx.net>
  2006-06-12 22:17 ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
  2006-06-12 22:25   ` Re: Partitioning... Milen Kulev <makulev@gmx.net>
  2006-06-12 22:58     ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
  2006-06-13 23:09       ` Re: Partitioning... Jim C. Nasby <jnasby@pervasive.com>
  2006-06-14 11:47         ` Re: Partitioning... Milen Kulev <makulev@gmx.net>
@ 2006-06-14 13:56           ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 32+ messages in thread

From: Tom Lane @ 2006-06-14 13:56 UTC (permalink / raw)
  To: Milen Kulev <makulev@gmx.net>; +Cc: 'Jim C. Nasby' <jnasby@pervasive.com>; pgsql-general

"Milen Kulev" <makulev@gmx.net> writes:
> Thanks Jim, the "trick" worked for me !

>> You might need to add an ORDER BY to the subquery to ensure
>> PostgreSQL doesn't pull it into the main query. 

Actually, the usual trick is "OFFSET 0", which works just as well as
an optimization fence and doesn't force any significant extra work.
Of course, if you *need* an ORDER BY then that's what to use, but
it's overkill if you just want to prevent flattening the subquery.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning...
  2006-06-12 22:02 Partitioning... Milen Kulev <makulev@gmx.net>
  2006-06-12 22:17 ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
@ 2006-06-12 22:34   ` Milen Kulev <makulev@gmx.net>
  1 sibling, 0 replies; 32+ messages in thread

From: Milen Kulev @ 2006-06-12 22:34 UTC (permalink / raw)
  To: pgsql-general

 BTW , I can not see the planner to prune/isolate the right table/partion ?
(constraint_exclusion=on, Version= 8.1.3 on RHEL4)

postgres=#  explain analyze  select count(id1) from part_all  where id1 =12 ;
                                                                   QUERY PLAN

---------------------------------------------------------------------------------------------------
 Aggregate  (cost=5951.74..5951.75 rows=1 width=4) (actual time=111.687..111.688 rows=1 loops=1)
   ->  Append  (cost=164.60..5463.19 rows=39084 width=49) (actual time=4.764..91.488 rows=13203 loops=1)
         ->  Bitmap Heap Scan on part_id1_0_10  (cost=164.60..3577.60 rows=25600 width=49) (actual time=0.123..0.123
rows=0 loops=1)
               Recheck Cond: (id1 = 12)
               ->  Bitmap Index Scan on idx_part_id1_0_10  (cost=0.00..164.60 rows=25600 width=0) (actual
time=0.119..0.119 rows=0 loops=1)
                     Index Cond: (id1 = 12)
         ->  Bitmap Heap Scan on part_id1_11_20  (cost=88.19..1494.74 rows=13484 width=49) (actual time=4.635..55.140
rows=13203 loops=1)
               Recheck Cond: (id1 = 12)
               ->  Bitmap Index Scan on idx_part_id1_11_20  (cost=0.00..88.19 rows=13484 width=0) (actual
time=4.067..4.067 rows=13205 loops=1)
                     Index Cond: (id1 = 12)
 Total runtime: 111.812 ms
(11 rows)

Time: 114.779 ms


Regards. Milen


-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Tom Lane
Sent: Tuesday, June 13, 2006 12:18 AM
To: Milen Kulev
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Partitioning... 


"Milen Kulev" <makulev@gmx.net> writes:
> But When I issue:
>  insert into  part(id1, id2, filler)
>  select  
>  round(  (random()*20)::bigint,0) as id1, <---!!! Note that both partitions should be populated!
>  round( (random()*20)::bigint,0) as id2,
>  'TTTTTTTTTTTESTTTTTZZZZZZZZZZZZZZZZZZZ'
>  from  generate_series(0,100000);

>  ERROR:  new row for relation "part_id1_0_10" violates check 
> constraint "part_id1_0_10_id1_check"

Don't use random() in your test case.

			regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
       subscribe-nomail command to majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly




^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Partitioning
@ 2007-08-18 08:09 Julio Cesar Sánchez González <knowhow@sistemasyconectividad.com.mx>
  2007-08-18 12:04 ` Re: Partitioning Robert Gravsjö <roppert@gmail.com>
  2007-08-18 15:33 ` Re: Partitioning Josh Tolley <eggyknap@gmail.com>
  0 siblings, 2 replies; 32+ messages in thread

From: Julio Cesar Sánchez González @ 2007-08-18 08:09 UTC (permalink / raw)
  To: pgsql-general

Hi guys,

It's natural what master table in the partitioning table contain data
(http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html) ?
or to be empty.

Thanks for all.

-- 
Regards,

Julio Cesar Sánchez González
www.sistemasyconectividad.com.mx
blog: http://darkavngr.blogspot.com

---
Ahora me he convertido en la muerte, destructora de mundos.
Soy la Muerte que se lleva todo, la fuente de las cosas que vendran.




^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning
  2007-08-18 08:09 Partitioning Julio Cesar Sánchez González <knowhow@sistemasyconectividad.com.mx>
@ 2007-08-18 12:04 ` Robert Gravsjö <roppert@gmail.com>
  1 sibling, 0 replies; 32+ messages in thread

From: Robert Gravsjö @ 2007-08-18 12:04 UTC (permalink / raw)
  To: Julio Cesar Sánchez González <knowhow@sistemasyconectividad.com.mx>; +Cc: pgsql-general

Julio Cesar Sánchez González wrote:
> Hi guys,
> 
> It's natural what master table in the partitioning table contain data
> (http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html) ?
> or to be empty.

Hi, the master table should be empty when doing partitioning.

Regards,
roppert

> 
> Thanks for all.
> 





^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning
  2007-08-18 08:09 Partitioning Julio Cesar Sánchez González <knowhow@sistemasyconectividad.com.mx>
@ 2007-08-18 15:33 ` Josh Tolley <eggyknap@gmail.com>
  1 sibling, 0 replies; 32+ messages in thread

From: Josh Tolley @ 2007-08-18 15:33 UTC (permalink / raw)
  To: Julio Cesar Sánchez González <knowhow@sistemasyconectividad.com.mx>; +Cc: pgsql-general

On 8/18/07, Julio Cesar Sánchez González
<knowhow@sistemasyconectividad.com.mx> wrote:
> Hi guys,
>
> It's natural what master table in the partitioning table contain data
> (http://www.postgresql.org/docs/8.1/interactive/ddl-partitioning.html) ?
> or to be empty.

I'm no partitioning expert, but I would say most of the time the
parent table is supposed to be empty. That said, partitioning schemes
need to be developed based on what data you have and what exactly
you're trying to do. If you're using the common example of keeping a
bunch of historical data where partitions represent a distinct chunk
of time (for instance, one partition for every month of data) you
probably want to keep the parent table empty. Every row has a date,
and so there's a child table for each row naturally, so it doesn't
make sense to put anything in the parent tables. But I'm sure someone
can come up with a scheme where having data in the parent is also
useful.

- Josh



^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* partitioning
@ 2012-08-11 00:53 John R Pierce <pierce@hogranch.com>
  2012-08-11 01:27 ` Re: partitioning John R Pierce <pierce@hogranch.com>
  0 siblings, 1 reply; 32+ messages in thread

From: John R Pierce @ 2012-08-11 00:53 UTC (permalink / raw)
  To: pgsql-general

we have a realtime manufacturing system that uses messaging to a data 
service that uses a very large database we're trying to migrate to 
postgres.  key tables in this database have a 6 month retention, and are 
quite large, so they are partitioned by week, and we drop the oldest 
weeks periodically.   Sometimes, the DROP TABLE's hang, then fail with a 
deadlock.

We are getting DROP TABLE deadlocks under the full production workload 
which involves 100 or so active database worker processes pretty much 
continuously doing transactions in respond to message from the floor.    
There are a lot of lookups in the partitioned tables parents that don't 
involve the oldest partitions, We've been unable to simulate this in my 
lab to date.    I've been unable to get ahold of a log of this event, as 
currently production has gone to manually stopping the workers and 
running the partition purge twice a month by hand, I'm asking them to 
reproduce so I can see whats going on.


this is kind of frustrating, because I'm so far removed from the 
problem, and am having problems getting adequate information.



-- 
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast





^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: partitioning
  2012-08-11 00:53 partitioning John R Pierce <pierce@hogranch.com>
@ 2012-08-11 01:27 ` John R Pierce <pierce@hogranch.com>
  0 siblings, 0 replies; 32+ messages in thread

From: John R Pierce @ 2012-08-11 01:27 UTC (permalink / raw)
  To: pgsql-general

On 08/10/12 6:00 PM, Jeff Potter wrote:
> Is it possible that you have any "idle in transaction" connections holding those tables open? See what you get when you show locks.

its very possible.   the developer thought he'd fixed all those and was 
surprised they were recurring.    they are coming from JDBC by default 
starting a transaction on a simple SELECT.  we don't 'commit' after 
selects as this code was written for Oracle, where plain selects dont 
have side effects like this.   this is only a problem in Postgres.   the 
intent is to use connections with 'autocommit on' for pure read 
threads.... however, this is a very large and complex code base, and 
site specific code is added by field engineering, so sometimes stuff 
slips in.


-- 
john r pierce                            N 37, W 122
santa cruz ca                         mid-left coast





^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Partitioning
@ 2015-01-19 01:58 James Sewell <james.sewell@lisasoft.com>
  2015-01-19 02:54 ` Re: Partitioning John R Pierce <pierce@hogranch.com>
  2015-01-19 11:58 ` Re: Partitioning François Beausoleil <francois@teksol.info>
  0 siblings, 2 replies; 32+ messages in thread

From: James Sewell @ 2015-01-19 01:58 UTC (permalink / raw)
  To: pgsql-general

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/sequential scans of every sub-table?

I want to select all rows which have an id which is in another query, so
something like:

  WITH idlist as (SELECT id from othertable)
    SELECT id from mastertable WHERE id = idlist.id);

I am guessing that I am not getting constraint exclusion to work as the
planner doesn't know the outcome of my subquery at plan time?

Any tricks I am overlooking?

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

-- 


------------------------------
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning
  2015-01-19 01:58 Partitioning James Sewell <james.sewell@lisasoft.com>
@ 2015-01-19 02:54 ` John R Pierce <pierce@hogranch.com>
  2015-01-19 03:13   ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
  1 sibling, 1 reply; 32+ messages in thread

From: John R Pierce @ 2015-01-19 02:54 UTC (permalink / raw)
  To: pgsql-general

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 <http://idlist.id;);


     select mt.id, ...   from mastertable mt join othertable ot on 
mt.id=ot.id;

might optimize better.



-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast

^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning
  2015-01-19 01:58 Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 02:54 ` Re: Partitioning John R Pierce <pierce@hogranch.com>
@ 2015-01-19 03:13   ` James Sewell <james.sewell@lisasoft.com>
  2015-01-19 06:09     ` Re: Partitioning Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
  2015-01-19 11:00     ` Re: Partitioning Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
  0 siblings, 2 replies; 32+ messages in thread

From: James Sewell @ 2015-01-19 03:13 UTC (permalink / raw)
  To: John R Pierce <pierce@hogranch.com>; +Cc: pgsql-general

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, John R Pierce <pierce@hogranch.com> wrote:

>  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                                      37N 122W
> somewhere on the middle of the left coast
>
>

-- 


------------------------------
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning
  2015-01-19 01:58 Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 02:54 ` Re: Partitioning John R Pierce <pierce@hogranch.com>
  2015-01-19 03:13   ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
@ 2015-01-19 06:09     ` Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
  2015-01-19 07:13       ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
  1 sibling, 1 reply; 32+ messages in thread

From: Kyotaro HORIGUCHI @ 2015-01-19 06:09 UTC (permalink / raw)
  To: james.sewell@lisasoft.com; +Cc: pierce@hogranch.com; pgsql-general

Hello,

an 2015 14:13:37 +1100, James Sewell <james.sewell@lisasoft.com> wrote in <CANkGpBs8GypQ3TQGKdjTD+n-w1rkq5uO97h3tuhg5eWaKR6RbA@mail.gmail.com>
> 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 the query *beforehand* execution. So the
criteria cannot rely on out of the query itself (and CHECK
constraints, of course).

Your query uses the result of the WITH-clause-query in the WHERE
clause which is unknown to the planner so constraint exclusion
does not work. JOINs don't change the situation.


> On Mon, Jan 19, 2015 at 1:54 PM, John R Pierce <pierce@hogranch.com> wrote:
> 
> >  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.

As the result, the query inevitably scans all the tables, but not
necessariry in sequqntial scans or simple index scans. The
suggestion above seeems showing the notation which the planner
can find the better plans on that premise.

For example, if you have an index on id of one of the two tables,
(and some other conditions match, of course) index only scan will
be selected for it and the suggested query will give you a
seemingly better plan than your query.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning
  2015-01-19 01:58 Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 02:54 ` Re: Partitioning John R Pierce <pierce@hogranch.com>
  2015-01-19 03:13   ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 06:09     ` Re: Partitioning Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
@ 2015-01-19 07:13       ` James Sewell <james.sewell@lisasoft.com>
  2015-01-19 07:23         ` Re: Partitioning John R Pierce <pierce@hogranch.com>
  0 siblings, 1 reply; 32+ messages in thread

From: James Sewell @ 2015-01-19 07:13 UTC (permalink / raw)
  To: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>; +Cc: pierce@hogranch.com <pierce@hogranch.com>; pgsql-general

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 <
horiguchi.kyotaro@lab.ntt.co.jp> wrote:

> Hello,
>
> an 2015 14:13:37 +1100, James Sewell <james.sewell@lisasoft.com
> <javascript:;>> wrote in <
> CANkGpBs8GypQ3TQGKdjTD+n-w1rkq5uO97h3tuhg5eWaKR6RbA@mail.gmail.com
> <javascript:;>>
> > 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 the query *beforehand* execution. So the
> criteria cannot rely on out of the query itself (and CHECK
> constraints, of course).
>
> Your query uses the result of the WITH-clause-query in the WHERE
> clause which is unknown to the planner so constraint exclusion
> does not work. JOINs don't change the situation.
>
>
> > On Mon, Jan 19, 2015 at 1:54 PM, John R Pierce <pierce@hogranch.com
> <javascript:;>> wrote:
> >
> > >  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.
>
> As the result, the query inevitably scans all the tables, but not
> necessariry in sequqntial scans or simple index scans. The
> suggestion above seeems showing the notation which the planner
> can find the better plans on that premise.
>
> For example, if you have an index on id of one of the two tables,
> (and some other conditions match, of course) index only scan will
> be selected for it and the suggested query will give you a
> seemingly better plan than your query.
>
> regards,
>
> --
> Kyotaro Horiguchi
> NTT Open Source Software Center
>
>

-- 

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

-- 


------------------------------
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning
  2015-01-19 01:58 Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 02:54 ` Re: Partitioning John R Pierce <pierce@hogranch.com>
  2015-01-19 03:13   ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 06:09     ` Re: Partitioning Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
  2015-01-19 07:13       ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
@ 2015-01-19 07:23         ` John R Pierce <pierce@hogranch.com>
  2015-01-19 07:59           ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
  0 siblings, 1 reply; 32+ messages in thread

From: John R Pierce @ 2015-01-19 07:23 UTC (permalink / raw)
  To: pgsql-general

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 likely.





-- 
john r pierce                                      37N 122W
somewhere on the middle of the left coast



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning
  2015-01-19 01:58 Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 02:54 ` Re: Partitioning John R Pierce <pierce@hogranch.com>
  2015-01-19 03:13   ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 06:09     ` Re: Partitioning Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
  2015-01-19 07:13       ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 07:23         ` Re: Partitioning John R Pierce <pierce@hogranch.com>
@ 2015-01-19 07:59           ` James Sewell <james.sewell@lisasoft.com>
  2015-01-19 08:14             ` Re: Partitioning John R Pierce <pierce@hogranch.com>
  0 siblings, 1 reply; 32+ messages in thread

From: James Sewell @ 2015-01-19 07:59 UTC (permalink / raw)
  To: John R Pierce <pierce@hogranch.com>; +Cc: pgsql-general

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 all index values?

Cheers,
James





On Monday, 19 January 2015, John R Pierce <pierce@hogranch.com> wrote:

> 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 likely.
>
>
>
>
>
> --
> john r pierce                                      37N 122W
> somewhere on the middle of the left coast
>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>


-- 

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

-- 


------------------------------
The contents of this email are confidential and may be subject to legal or 
professional privilege and copyright. No representation is made that this 
email is free of viruses or other defects. If you have received this 
communication in error, you may not copy or distribute any part of it or 
otherwise disclose its contents to anyone. Please advise the sender of your 
incorrect receipt of this correspondence.

^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning
  2015-01-19 01:58 Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 02:54 ` Re: Partitioning John R Pierce <pierce@hogranch.com>
  2015-01-19 03:13   ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 06:09     ` Re: Partitioning Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
  2015-01-19 07:13       ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 07:23         ` Re: Partitioning John R Pierce <pierce@hogranch.com>
  2015-01-19 07:59           ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
@ 2015-01-19 08:14             ` John R Pierce <pierce@hogranch.com>
  2015-01-19 08:42               ` Re: Partitioning Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
  0 siblings, 1 reply; 32+ messages in thread

From: John R Pierce @ 2015-01-19 08:14 UTC (permalink / raw)
  To: pgsql-general

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 <http://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                                      37N 122W
somewhere on the middle of the left coast

^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning
  2015-01-19 01:58 Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 02:54 ` Re: Partitioning John R Pierce <pierce@hogranch.com>
  2015-01-19 03:13   ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 06:09     ` Re: Partitioning Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
  2015-01-19 07:13       ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 07:23         ` Re: Partitioning John R Pierce <pierce@hogranch.com>
  2015-01-19 07:59           ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 08:14             ` Re: Partitioning John R Pierce <pierce@hogranch.com>
@ 2015-01-19 08:42               ` Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
  0 siblings, 0 replies; 32+ messages in thread

From: Kyotaro HORIGUCHI @ 2015-01-19 08:42 UTC (permalink / raw)
  To: pierce@hogranch.com; +Cc: pgsql-general

Hi,

At Mon, 19 Jan 2015 00:14:55 -0800, John R Pierce <pierce@hogranch.com> wrote in <54BCBCFF.5040100@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 <http://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.

Yeah, scanning children reading highest/lowest values for each of
them would make it faster than the first single query.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning
  2015-01-19 01:58 Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 02:54 ` Re: Partitioning John R Pierce <pierce@hogranch.com>
  2015-01-19 03:13   ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
@ 2015-01-19 11:00     ` Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
  1 sibling, 0 replies; 32+ messages in thread

From: Kyotaro HORIGUCHI @ 2015-01-19 11:00 UTC (permalink / raw)
  To: pierce@hogranch.com; pgsql-general

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

^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning
  2015-01-19 01:58 Partitioning James Sewell <james.sewell@lisasoft.com>
@ 2015-01-19 11:58 ` François Beausoleil <francois@teksol.info>
  2015-01-20 01:12   ` Re: Partitioning Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
  1 sibling, 1 reply; 32+ messages in thread

From: François Beausoleil @ 2015-01-19 11:58 UTC (permalink / raw)
  To: James Sewell <james.sewell@lisasoft.com>; +Cc: pgsql-general


> Le 2015-01-18 à 20:58, James Sewell <james.sewell@lisasoft.com> 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 working using constraint exclusion, or am I doomed to do index/sequential scans of every sub-table?
> 
> I want to select all rows which have an id which is in another query, so something like:
> 
>   WITH idlist as (SELECT id from othertable) 
>     SELECT id from mastertable WHERE id = idlist.id);
> 
> I am guessing that I am not getting constraint exclusion to work as the planner doesn't know the outcome of my subquery at plan time?
> 
How many rows in idlist? Can you do two queries? Fetch the ids, then call the 2nd query with those values hard-coded in the query.

If necessary, and you know where each partition starts, tou could sort in the app and query the correct ranges, in multiple queries.

Hope that helps!
François Beausoleil=

^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning
  2015-01-19 01:58 Partitioning James Sewell <james.sewell@lisasoft.com>
  2015-01-19 11:58 ` Re: Partitioning François Beausoleil <francois@teksol.info>
@ 2015-01-20 01:12   ` Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
  0 siblings, 0 replies; 32+ messages in thread

From: Kyotaro HORIGUCHI @ 2015-01-20 01:12 UTC (permalink / raw)
  To: francois@teksol.info; +Cc: james.sewell@lisasoft.com; pgsql-general

Hi, 

19 Jan 2015 06:58:21 -0500, François Beausoleil <francois@teksol.info> wrote in <CC2FD572-320A-4225-B98C-48F20992527C@teksol.info>
> 
> > Le 2015-01-18 à 20:58, James Sewell <james.sewell@lisasoft.com> 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 working using constraint exclusion, or am I doomed to do index/sequential scans of every sub-table?
> > 
> > I want to select all rows which have an id which is in another query, so something like:
> > 
> >   WITH idlist as (SELECT id from othertable) 
> >     SELECT id from mastertable WHERE id = idlist.id);
> > 
> > I am guessing that I am not getting constraint exclusion to work as the planner doesn't know the outcome of my subquery at plan time?
> > 
> How many rows in idlist? Can you do two queries? Fetch the ids, then call the 2nd query with those values hard-coded in the query.
> 
> If necessary, and you know where each partition starts, tou could sort in the app and query the correct ranges, in multiple queries.

The strategy's effectiveness is depends mainly on how many ids
come from the othertable. It wins if fewer than certain number or
converged in a few partitions, however, straight joins will win
elsewise.

The result of EXPLAIN ANALYZE might draw more precise advices.

regards,

-- 
Kyotaro Horiguchi
NTT Open Source Software Center



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Partitioning
@ 2017-07-25 12:51 Krithika Venkatesh <krithikavenkatesh31@gmail.com>
  2017-07-26 20:22 ` Re: Partitioning George Neuner <gneuner2@comcast.net>
  0 siblings, 1 reply; 32+ messages in thread

From: Krithika Venkatesh @ 2017-07-25 12:51 UTC (permalink / raw)
  To: pgsql-general

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 INNER JOIN TABLE B b ON  a.ID = b.ID.

Is there any other option that would work.

Thanks in Advance..

^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: Partitioning
  2017-07-25 12:51 Partitioning Krithika Venkatesh <krithikavenkatesh31@gmail.com>
@ 2017-07-26 20:22 ` George Neuner <gneuner2@comcast.net>
  0 siblings, 0 replies; 32+ messages in thread

From: George Neuner @ 2017-07-26 20:22 UTC (permalink / raw)
  To: pgsql-general

On Tue, 25 Jul 2017 18:21:43 +0530, Krithika Venkatesh
<krithikavenkatesh31@gmail.com> 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)
>
>Partitioning doesn't work when I do join.
>
>SELECT A.* FROM TABLE A a INNER JOIN TABLE B b ON  a.ID = b.ID.
>
>Is there any other option that would work.
>
>Thanks in Advance..

The subselect is constraining the set of ID value(s) to be matched in
A, which (at least potentially) permits identifying the relevant
partition(s).

The join must include all partitions of A because the set of ID values
to be matched with B are not constrained.

Also, the join query is not equivalent because it does not include the
timestamp constraint on B.  I don't think that will make any
difference to the query plan ... AFAICS, it still needs to consider
all partitions of A ... but it may improve performance.

George



-- 
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general



^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* partitioning
@ 2023-10-23 21:36 Torsten Förtsch <tfoertsch123@gmail.com>
  2023-10-24 02:13 ` Re: partitioning David Rowley <dgrowleyml@gmail.com>
  0 siblings, 1 reply; 32+ messages in thread

From: Torsten Förtsch @ 2023-10-23 21:36 UTC (permalink / raw)
  To: pgsql-general <pgsql-general@lists.postgresql.org>

Hi,

I have an old, several TB table. That table has a child table (table
inheritance) which is much smaller. Each row represents a certain process.
The original idea was while the process is in progress it is stored in the
small table. Once it has reached its final state, it is moved to the big
one.

Now I want to convert this to the declarative partitioning scheme and in
that process introduce a new partition for finished processes.

The fact that a process is finished is represented by the boolean column
is_sold. The table has also a timestamp column called purchase_time. The
new partitioning should be as follows

CREATE TABLE ... (...)
PARTITION BY RANGE (is_sold, purchase_time);

Then I want to reattach the original small table with the unfinished
processes like so:

ALTER TABLE ... ATTACH PARTITION original_small_table
  FOR VALUES FROM (false, '-infinity') TO (false, 'infinity');

Reattach the big table like so:

ALTER TABLE ... ATTACH PARTITION original_big_table
  FOR VALUES FROM (true, '-infinity') TO (true, 'tomorrow');

And create a new default partition for the rest, the newly finished
processes:

CREATE TABLE ... PARTITION OF ... DEFAULT;

Both is_sold and purchase_time have a NOT NULL constraint. The small table
also has a check constraint CHECK(NOT is_sold).

Now, the documentation (
https://www.postgresql.org/docs/14/ddl-partitioning.html) says, if the
table that's going to be attached as a partition has a constraint that
ensures the partition boundaries, the check while attaching it can be
avoided:

> Before running the ATTACH PARTITION command, it is recommended to create
a CHECK constraint on the table to be
> attached that matches the expected partition constraint, as illustrated
above. That way, the system will be able to skip the
> scan which is otherwise needed to validate the implicit partition
constraint.

So, initially I thought that check constraint should be enough. But it was
not.

Then I added this constraint to the small table:

ALTER TABLE original_small_table
ADD CONSTRAINT partition_boundaries
CHECK((false, '-infinity')<=(is_sold, purchase_time)
  AND (is_sold, purchase_time)<(false, 'infinity'))
  NOT VALID;

And validated it.

When this is being attached as a partition, I still can see the process is
reading the entire table.

What am I missing? What should the check constraint look like in my case to
match the partition constraint?

This is PG 14.

Thanks,
Torsten

^ permalink  raw  reply  [nested|flat] 32+ messages in thread

* Re: partitioning
  2023-10-23 21:36 partitioning Torsten Förtsch <tfoertsch123@gmail.com>
@ 2023-10-24 02:13 ` David Rowley <dgrowleyml@gmail.com>
  0 siblings, 0 replies; 32+ messages in thread

From: David Rowley @ 2023-10-24 02:13 UTC (permalink / raw)
  To: Torsten Förtsch <tfoertsch123@gmail.com>; +Cc: pgsql-general <pgsql-general@lists.postgresql.org>

On Tue, 24 Oct 2023 at 10:39, Torsten Förtsch <tfoertsch123@gmail.com> wrote:
> Then I added this constraint to the small table:
>
> ALTER TABLE original_small_table
> ADD CONSTRAINT partition_boundaries
> CHECK((false, '-infinity')<=(is_sold, purchase_time)
>   AND (is_sold, purchase_time)<(false, 'infinity'))
>   NOT VALID;
>
> And validated it.
>
> When this is being attached as a partition, I still can see the process is reading the entire table.

I'm not seeing any handling of RowCompareExpr in predtest.c, so it
appears your row comparisons can't be used to prove that the CHECK
constraint implies the partition constraint.

> What am I missing? What should the check constraint look like in my case to match the partition constraint?

The following should work:

ALTER TABLE original_small_table ADD CONSTRAINT partition_boundaries
CHECK(not is_sold and purchase_time >= '-infinity' and purchase_time <
'infinity');

David





^ permalink  raw  reply  [nested|flat] 32+ messages in thread


end of thread, other threads:[~2023-10-24 02:13 UTC | newest]

Thread overview: 32+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-24 22:00 partitioning Dennis Gearon <gearond@fireserve.net>
2003-10-24 22:35 ` scott.marlowe <scott.marlowe@ihs.com>
2006-06-12 22:02 Partitioning... Milen Kulev <makulev@gmx.net>
2006-06-12 22:17 ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
2006-06-12 22:25   ` Re: Partitioning... Milen Kulev <makulev@gmx.net>
2006-06-12 22:58     ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
2006-06-12 23:04       ` Re: Partitioning... Milen Kulev <makulev@gmx.net>
2006-06-13 23:09       ` Re: Partitioning... Jim C. Nasby <jnasby@pervasive.com>
2006-06-14 11:47         ` Re: Partitioning... Milen Kulev <makulev@gmx.net>
2006-06-14 13:56           ` Re: Partitioning... Tom Lane <tgl@sss.pgh.pa.us>
2006-06-12 22:34   ` Re: Partitioning... Milen Kulev <makulev@gmx.net>
2007-08-18 08:09 Partitioning Julio Cesar Sánchez González <knowhow@sistemasyconectividad.com.mx>
2007-08-18 12:04 ` Re: Partitioning Robert Gravsjö <roppert@gmail.com>
2007-08-18 15:33 ` Re: Partitioning Josh Tolley <eggyknap@gmail.com>
2012-08-11 00:53 partitioning John R Pierce <pierce@hogranch.com>
2012-08-11 01:27 ` John R Pierce <pierce@hogranch.com>
2015-01-19 01:58 Partitioning James Sewell <james.sewell@lisasoft.com>
2015-01-19 02:54 ` Re: Partitioning John R Pierce <pierce@hogranch.com>
2015-01-19 03:13   ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
2015-01-19 06:09     ` Re: Partitioning Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
2015-01-19 07:13       ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
2015-01-19 07:23         ` Re: Partitioning John R Pierce <pierce@hogranch.com>
2015-01-19 07:59           ` Re: Partitioning James Sewell <james.sewell@lisasoft.com>
2015-01-19 08:14             ` Re: Partitioning John R Pierce <pierce@hogranch.com>
2015-01-19 08:42               ` Re: Partitioning Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
2015-01-19 11:00     ` Re: Partitioning Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
2015-01-19 11:58 ` Re: Partitioning François Beausoleil <francois@teksol.info>
2015-01-20 01:12   ` Re: Partitioning Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>
2017-07-25 12:51 Partitioning Krithika Venkatesh <krithikavenkatesh31@gmail.com>
2017-07-26 20:22 ` Re: Partitioning George Neuner <gneuner2@comcast.net>
2023-10-23 21:36 partitioning Torsten Förtsch <tfoertsch123@gmail.com>
2023-10-24 02:13 ` David Rowley <dgrowleyml@gmail.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox