From: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Kevin Grittner <kgrittn@ymail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: Gunnlaugur Thor Briem <gunnlaugur@gmail.com>
Subject: Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT
Date: Wed, 11 Mar 2015 18:55:08 +0100
Message-ID: <5500817C.3030609@2ndquadrant.com> (raw)
In-Reply-To: <CAMkU=1ywKaeECzjVpJUWEYPkA1AFGqL37tQxQ2j_pdXfkp8RrA@mail.gmail.com>
References: <54F9E179.4000804@2ndquadrant.com>
<1407502480.21180.1425691482543.JavaMail.yahoo@mail.yahoo.com>
<27245.1425692294@sss.pgh.pa.us>
<CAMkU=1zxAB4AQySxex_DXP2mt+Ab=S+nQw+2M1rY7CFJzgYfTQ@mail.gmail.com>
<54FB1CDA.1080606@2ndquadrant.com>
<CAMkU=1ywKaeECzjVpJUWEYPkA1AFGqL37tQxQ2j_pdXfkp8RrA@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 11.3.2015 18:30, Jeff Janes wrote:
> On Sat, Mar 7, 2015 at 7:44 AM, Tomas Vondra
> <tomas.vondra@2ndquadrant.com <mailto:tomas.vondra@2ndquadrant.com>> wrote:
>
> On 7.3.2015 03:26, Jeff Janes wrote:
> > On Fri, Mar 6, 2015 at 5:38 PM, Tom Lane <tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>
> > <mailto:tgl@sss.pgh.pa.us <mailto:tgl@sss.pgh.pa.us>>> wrote:
> >
> > But the actual query is using a seq scan, and so it would hint the
> > table in efficient sequential order, rather than hinting the table
> > haphazardly in index order like probing the endpoint does.
>
> I think this has nothing to do with the plan itself, but with the
> estimation in optimizer - that looks up the range from the index in some
> cases, and that may generate random I/O to the table.
>
>
> Right. Tom was saying that the work needs to be done anyway, but it is
> just that some ways of doing the work are far more efficient than
> others. It just happens that the executed plan in this case would do it
> more efficiently, (but in general you aren't going to get any less
> efficient than having the planner do it in index order).
Oh! Now I see what you meant. I parsed is as if you're suggesting that
the theory does not match the symptoms because the plan contains
sequential scan yet there's a lot of random I/O. But now I see that's
not what you claimed, so sorry for the noise.
--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
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: tomas.vondra@2ndquadrant.com, jeff.janes@gmail.com, tgl@sss.pgh.pa.us, kgrittn@ymail.com, gunnlaugur@gmail.com
Subject: Re: EXPLAIN (no ANALYZE) taking an hour for INSERT FROM SELECT
In-Reply-To: <5500817C.3030609@2ndquadrant.com>
* 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