Re: partitioned table set and indexes

I do not know why if I blast a new index creation on the 20 or so children
all at once some of them fail, but then if I go back and do a few at a time
they all work. It has happened to me 3 times now, so I'm pretty sure I'm
not imagining it.

What specifically in the explain analyze output tells you that it is using
a sequence scan instead of an index scan _because_ there are too few rows?
I can see where it chooses a sequence scan over an index and I know there
are only a few rows in those tables, but I'm not sure how the explain
output tells you that it made that choice on purpose.

Why would the select statement use the index, but not the join?

There used to be an explain output anonymizer tool, if I can find that
again, I'll send along the output. It has been a few years since I posted
a question to this list so I don't think I have a bookmark for it any
more.... Hmmm. I'll look around.

Meanwhile:

--

select
*
from
my_parent_table
where
mypk = 'something';

Uses an index scan on each of my_parent_table's children except for a
couple of them that don't have a lot of rows, and those are sequence
scanned. (which is ok)

--

select
*
from
some_other_table sot
join my_parent_table mpt on sot.some_column = mpt.mypk
where
sot.another_column = 'q'

Sequence scans each of my_parent_table's children. (It doesn't matter
which order I put the join.)

--

select
*
from
some_other_table sot
join my_parent_table mpt on sot.some_column = mpt.mypk
where
mpt.column_3 = 'z'
and
sot.another_column = 'q'

Index scans my_parent_table's children on column_3 (except for the couple
with only a few rows), and doesn't sequence scan for the mypk column at all.

On Fri, Dec 11, 2015 at 2:44 PM, Andreas Kretschmer <
akretschmer(at)spamfence(dot)net> wrote:

> Rick Otten <rottenwindfish(at)gmail(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>