Re: R: R: [GENERAL] Slow queries on very big (and partitioned) table

2017-02-21 Thread Tom Lane
Stephen Frost writes: > * Job (j...@colliniconsulting.it) wrote: >> We thought to implement one partition for day. >> Do you think it should be fine? > Really depends on what you're doing. If you're running very short > queries that pull out just a record or a few records,

Re: R: R: [GENERAL] Slow queries on very big (and partitioned) table

2017-02-21 Thread Stephen Frost
Greetings, * Job (j...@colliniconsulting.it) wrote: > >If this is really what you're mostly doing, having constraint exclusion and > >an index on 'profile' would probably be enough, if you insist on continuing > >to have the table partitioned by day (which I continue to argue is a bad > >idea-

Re: R: [GENERAL] Slow queries on very big (and partitioned) table

2017-02-21 Thread Rakesh Kumar
> We thought to implement one partition for day. That would be 365 partitions in a year. In our experience INSERTS suffers the most in a partitioned table because triggers are the only way to route the row to the proper child (inherited) table. Question: How is your insert pattern? Do you

R: R: [GENERAL] Slow queries on very big (and partitioned) table

2017-02-21 Thread Job
Hi Stephen, Thank you for your excellent opinion! >If this is really what you're mostly doing, having constraint exclusion and an >index on 'profile' would probably be enough, if you insist on continuing to >have the table partitioned by day (which I continue to argue is a bad idea- >based on

Re: R: [GENERAL] Slow queries on very big (and partitioned) table

2017-02-20 Thread Steven Winfield
> Unfortunately, that'll require locking each table and scanning it to make > sure that the CHECK constraint isn't violated. Actually, CHECK constraints can be added with the NOT VALID clause. New tuples will be checked immediately, while the validation of existing tuples can be done later

Re: R: [GENERAL] Slow queries on very big (and partitioned) table

2017-02-20 Thread Stephen Frost
Greetings, * Job (j...@colliniconsulting.it) wrote: > here is primary a partitioned table (for 20/2/2017 logs): > flashstart=# \d webtraffic_archive_day_2017_02_20; > Table > "public.webtraffic_archive_day_2017_02_20" > Column |Type

R: [GENERAL] Slow queries on very big (and partitioned) table

2017-02-20 Thread Job
Hi, here is primary a partitioned table (for 20/2/2017 logs): flashstart=# \d webtraffic_archive_day_2017_02_20; Table "public.webtraffic_archive_day_2017_02_20" Column |Type | Modifiers