Re: plan time of MASSIVE partitioning ...

From: Stephen Frost <sfrost(at)snowman(dot)net>
To: PostgreSQL - Hans-Jürgen Schönig <postgres(at)cybertec(dot)at>
Cc: pgsql-hackers Hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: plan time of MASSIVE partitioning ...
Date: 2010-09-03 12:27:36
Message-ID: 20100903122736.GQ26232@tamriel.snowman.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

* PostgreSQL - Hans-Jürgen Schönig (postgres(at)cybertec(dot)at) wrote:
> this seems like more a developer question to me than a pre performance one.
> it is not related to the table structure at all - it is basically an issue with incredibly large inheritance lists.
> it applies to postgres 9 and most likely to everything before.
> postgresql.conf is not relevant at all at this point.

Really? What's constraint_exclusion set to? Is GEQO getting used?
What are the GEQO parameters set to? Do you have any CHECK constraints
on the tables?

If you want someone else to build a test case and start looking into it,
it's best to not make them have to guess at what you've done.

> the plan is pretty fine.
> the question is rather: does anybody see a chance to handle such lists more efficiently inside postgres?
> also, it is not the point if my data structure is sane or not. it is really more generic - namely a shortcut for this case inside the planing process.

Coming up with cases where PG doesn't perform well in a completely
contrived unrealistic environment isn't likely to impress anyone to
do anything.

A small (but complete..) test case which mimics a real world environment
and real world problem would go alot farther. I can certainly believe
that people out there have partitions set up with lots of tables and
that it will likely grow- but they probably also have CHECK constraints,
have tweaked what constraint_exclusion is set to, have adjusted their
work_mem and related settings, maybe tweaked some planner GUCs, etc,
etc.

This is an area I'm actually interested in and curious about, I'd rather
work together on it than be told that the questions I'm asking aren't
relevant.

Thanks,

Stephen

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Heikki Linnakangas 2010-09-03 12:28:58 Re: Streaming a base backup from master
Previous Message Greg Stark 2010-09-03 12:16:25 Re: Streaming a base backup from master