From: Andreas Kretschmer <andreas@a-kretschmer.de>
To: Rick Otten <rottenwindfish@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: partitioned table set and indexes
Date: Fri, 11 Dec 2015 22:44:05 +0100 (CET)
Message-ID: <1203683949.315121.1449870245520.JavaMail.open-xchange@oxweb02.ims-firmen.de> (raw)
In-Reply-To: <CAMAYy4JpF8oAihsqsSvtUcMMetLt=KWVqFrVdChqDwMhKci8gA@mail.gmail.com>
References: <CAMAYy4LGhyd1yUjBsmhmY7nSgb9rX28r4nad=iuGDQaAUacDFQ@mail.gmail.com>
<20151211194440.GA14772@tux>
<CAMAYy4JpF8oAihsqsSvtUcMMetLt=KWVqFrVdChqDwMhKci8gA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
> Rick Otten <rottenwindfish@gmail.com> hat am 11. Dezember 2015 um 21:40
> geschrieben:
>
>
> 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.
don't believe that, sorry.
>
> 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.
a sequentiell scan over a small table are cheaper than an index-scan. Imageine a
small table,
only 3 rows. Fits in one page. It's cheaper to read just this page than read the
index
plus read the table to put out the result row.
Why are you using partitioning? That's make only sense with large child-tables
(more than 1 million rows or so)
and if you have a proper partitioning schema.
>
> 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.
http://explain.depesz.com/
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: andreas@a-kretschmer.de, rottenwindfish@gmail.com
Subject: Re: partitioned table set and indexes
In-Reply-To: <1203683949.315121.1449870245520.JavaMail.open-xchange@oxweb02.ims-firmen.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox