Date: Fri, 11 Dec 2015 20:44:40 +0100
From: Andreas Kretschmer <akretschmer@spamfence.net>
To: pgsql-performance@postgresql.org
Subject: Re: partitioned table set and indexes
Message-ID: <20151211194440.GA14772@tux>
References: 
 <CAMAYy4LGhyd1yUjBsmhmY7nSgb9rX28r4nad=iuGDQaAUacDFQ@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=iso-8859-1
Content-Disposition: inline
Content-Transfer-Encoding: 8bit
In-Reply-To: 
 <CAMAYy4LGhyd1yUjBsmhmY7nSgb9rX28r4nad=iuGDQaAUacDFQ@mail.gmail.com>
User-Agent: Mutt/1.5.18 (2008-05-17)
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

Rick Otten <rottenwindfish@gmail.com> wrote:

> I'm using PostgreSQL 9.5 Beta 2.
> 
> I am working with a partitioned table set.
> 
> The first thing I noticed, when creating indexes on the 20 or so partitions,
> was that if I create them too fast they don't all succeed.  I have to do a few
> at a time, let them breathe for a few seconds, and then do a few more.   I had
> been simply generating all of the create index commands in a text editor, and
> then cutting and pasting the lot of them into psql all at once or running them
> by using psql '-f'.  Most would get created, but not all.  It seems almost
> random.  There were no obvious error messages.  When I do a few at a time, it
> is never an issue.

Sure? Have you checked that?


> If I do a simple query with a where clause on a specific column from the parent
> table, I can see it index scan each of the children.  This is what I want it to
> do, so no complaints there.
> 
> However, if I try to (inner) join another table with that column, the planner
> sequence scans each of the children instead of using the indexes.  I saw
> someone had posted a similar question to this list back in January, however I
> didn't see the answer.

Show us the output from explain analyse <your query>


> FWIW, the column in question is a UUID column and is the primary key for each
> of the child tables.


PostgreSQL using a cost-modell, so maybe there are not enough rows in
the table. That's just a guess, you can see that with explain analyse
... 


Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance