On 10/25/07, tfinneid@student.matnat.uio.no
<tfinneid@student.matnat.uio.no> wrote:
> > Alvaro Herrera <alvherre@commandprompt.com> writes:
> >> tfinneid@student.matnat.uio.no wrote:
> >>> I did a test previously, where I created 1 million partitions (without
> >>> data) and I checked the limits of pg, so I think it should be ok.
> >
> >> Clearly it's not.
> >
> > You couldn't have tested it too much --- even planning a query over so
> > many tables would take forever, and actually executing it would surely
> > have run the system out of locktable space before it even started
> > scanning.
>
> And this is the testing, so you're right....
>
> Its only the select on the root table that fails. Operations on a single
> partitions is no problem.
Not sure I understand exactly what you're saying.
Are you selecting directly from the child table, or from the parent
table with constraint_exclusion turned on?
If you're hitting the child table directly, you aren't actually using
partitioning. It's a wholly independent table at that point.
If you're hitting a single child table through the parent table via
constraint_exclusion, then you are using partitioning, but only
hitting on physical table.
But hitting the parent table with no constraining where clause is a
recipe for disaster. The very reason to use partitioning is so that
you never have to scan through a single giant table.
Anyway, you're heading off into new territory with 55,000 partitions.
What is the average size, in MB of one of your partitions? I found
with my test, there was a point of diminishing returns after 400 or so
partitions at which point indexes were no longer needed, because the
average query just seq scanned the partitions it needed, and they were
all ~ 16 or 32 Megs.