Re: partitioned table set and indexes - Mailing list pgsql-performance
| From | Rick Otten |
|---|---|
| Subject | Re: partitioned table set and indexes |
| Date | |
| Msg-id | CAMAYy4JpF8oAihsqsSvtUcMMetLt=KWVqFrVdChqDwMhKci8gA@mail.gmail.com Whole thread Raw |
| In response to | Re: partitioned table set and indexes (Andreas Kretschmer <akretschmer@spamfence.net>) |
| Responses |
Re: partitioned table set and indexes
(Andreas Kretschmer <andreas@a-kretschmer.de>)
|
| List | pgsql-performance |
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.
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@spamfence.net> wrote:
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
pgsql-performance by date: