Re: partitioned table set and indexes

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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@xxxxxxxxxxxxx> wrote:
Rick Otten <rottenwindfish@xxxxxxxxx> 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@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance


[Postgresql General]     [Postgresql PHP]     [PHP Users]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Yosemite]

  Powered by Linux