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: [GENERAL] Slow queries on very big (and partitioned) table

2017-02-20 Thread John R Pierce
On 2/20/2017 5:22 AM, Stephen Frost wrote: You probably shouldn't be partitioning by day for such a small dataset, unless you've only got a few days worth of data that make up those 800m records. agreed. we do like 6 months retention by weeks, so there's 26 or so partitions, that is

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
Index Cond: (("timestamp")::date = '2017-02-20'::date) -> Bitmap Heap Scan on webtraffic_archive_day_2017_02_19 (cost=120.00..160.02 rows=1 width=70) (actual time=4696.922..4696.922 rows=0 loops=1) Recheck Cond: (("timestamp")::date = '

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

2017-02-20 Thread Stephen Frost
Greetings, * Job (j...@colliniconsulting.it) wrote: > we have a test machine with Postgresql 9.6.1 an about 800.000.000 record in a > table. > Table is partitioned by day, with indexes on partitioned table. You probably shouldn't be partitioning by day for such a small dataset, unless you've

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

2017-02-20 Thread Jaime Soler
Please share us an explain analyze of your query and \d+ of your table 2017-02-20 13:33 GMT+01:00 Job : > Hu guys, > > we have a test machine with Postgresql 9.6.1 an about 800.000.000 record > in a table. > Table is partitioned by day, with indexes on partitioned

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

2017-02-20 Thread Job
Hu guys, we have a test machine with Postgresql 9.6.1 an about 800.000.000 record in a table. Table is partitioned by day, with indexes on partitioned table. Also a simple query (for example i want to search log occurred in a specific day), is immediate in tha partitioned table