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
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
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
> 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
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
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
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
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
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
> 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
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
11 matches
Mail list logo
