Обсуждение: [GENERAL] Search on very big (partitioned) table

Поиск
Список
Период
Сортировка

[GENERAL] Search on very big (partitioned) table

От
"Collini, ColliniConsulting.it"
Дата:
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 (table_2017_02_15) but *extremely* slow in global table.
 
Where am i wrong?

Thank you!
/F
 

Re: [GENERAL] Search on very big (partitioned) table

От
Adam Brusselback
Дата:
Do you have non overlapping check constraints on the partitions by date to allow the planner to exclude the child tables from needing to be looked at?

Re: [GENERAL] Search on very big (partitioned) table

От
Sushant Pawar
Дата:
What is the execution plan of a query is showing? Check whether it is reading through all partitions.

Best Regards,

Sushant Pawar | Database Solution Consultant

ASHNIK TECHNOLOGY SOLUTIONS PVT. LTD.

Skype: sush_531 | T: +91 9769559995 | www.ashnik.com



On Tue, Feb 21, 2017 at 4:47 AM, Adam Brusselback <adambrusselback@gmail.com> wrote:
Do you have non overlapping check constraints on the partitions by date to allow the planner to exclude the child tables from needing to be looked at?

R: [GENERAL] Search on very big (partitioned) table

От
Job
Дата:
Hi Sushant,
 
i think the problem is that we miss check constraints.
We will implement asap....
 
For the moment thank you to everybody for the excellent support!
 
/F