From: Jim Nasby <Jim.Nasby@BlueTreble.com>
To: Craig James <cjames@emolecules.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: limit 1 on view never finishes
Date: Wed, 2 Nov 2016 18:37:03 -0500
Message-ID: <c4ef50c0-1253-e0b7-477d-f1b101dc2865@BlueTreble.com> (raw)
In-Reply-To: <CAFwQ8rcOD1J+FnaohjY7UcLsUUpiDkCROKFCZpcMcUXKuic0+A@mail.gmail.com>
References: <CAFwQ8rcOD1J+FnaohjY7UcLsUUpiDkCROKFCZpcMcUXKuic0+A@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
On 10/27/16 3:46 PM, Craig James wrote:
>
> Limit (cost=3264.63..7193.14 rows=1 width=4)
> -> Nested Loop (cost=3264.63..428658697.57 rows=109114 width=4)
> Join Filter: (rv.version_id = sample.version_id)
> -> Index Only Scan Backward using version_pkey on version rv
> (cost=0.42..6812.85 rows=261895 width=4)
> -> Materialize (cost=3264.21..5992.06 rows=109114 width=4)
> -> HashAggregate (cost=3264.21..4355.35 rows=109114
> width=4)
> -> Seq Scan on sample (cost=0.00..2991.37
> rows=109137 width=4)
>
> Why would this trivial query run forever at 100% CPU?
My bet is that there's a lot of rows in version that have a higher
version than what's in sample. That means a lot of repeated scans
through the tuplestore underneath the Materialize node.
If you can remove duplicates from sample and get rid of the DISTINCT,
this will probably get a better plan. If you can't do that then you
could try changing the JOIN to an IN:
SELECT ... FROM sample
WHERE version_id IN (SELECT version_id FROM sample)
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com
855-TREBLE2 (855-873-2532) mobile: 512-569-9461
--
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: Jim.Nasby@BlueTreble.com, cjames@emolecules.com
Subject: Re: limit 1 on view never finishes
In-Reply-To: <c4ef50c0-1253-e0b7-477d-f1b101dc2865@BlueTreble.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