Thread: [GENERAL] explain analyze output: 0 rows, 1M loopa
So some of my output from an explain analyze here has a line that says this: ex Scan using warranty_order_item_warranty_order_id_idx on warranty_order_item woi_1 (cost=0.57..277.53 rows=6 width=137) (actual time=0.110..0.111 rows=0 loops=1,010,844) How can you have 1M loops over 0 rows? Running on PostgreSQL 9.5.6. -- To understand recursion, one must first understand recursion. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
So some of my output from an explain analyze here has a line that says this:
ex Scan using warranty_order_item_warranty_order_id_idx on
warranty_order_item woi_1 (cost=0.57..277.53 rows=6 width=137) (actual
time=0.110..0.111 rows=0 loops=1,010,844)
Not my strong suit but, I'm pretty sure that reads: "The index was queried 1M+ times and none of those inqueries resulted in a record being found". IIUC I'd be wondering why some form of hash join wasn't used...
David J.
On Wed, Nov 01, 2017 at 12:19:21PM -0700, David G. Johnston wrote: > On Wed, Nov 1, 2017 at 11:59 AM, Scott Marlowe <scott.marlowe@gmail.com> > wrote: > > > So some of my output from an explain analyze here has a line that says > > this: > > > > ex Scan using warranty_order_item_warranty_order_id_idx on > > warranty_order_item woi_1 (cost=0.57..277.53 rows=6 width=137) (actual > > time=0.110..0.111 rows=0 loops=1,010,844) > > > > Not my strong suit but, I'm pretty sure that reads: "The index was queried > 1M+ times and none of those inqueries resulted in a record being found". > IIUC I'd be wondering why some form of hash join wasn't used... Except that: https://www.postgresql.org/docs/current/static/using-explain.html "... the loops value reports the total number of executions of the node, and the actual time and ROWS VALUES SHOWN ARE AVERAGES PER-EXECUTION." Justin -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On Wed, Nov 01, 2017 at 12:19:21PM -0700, David G. Johnston wrote:
> On Wed, Nov 1, 2017 at 11:59 AM, Scott Marlowe <scott.marlowe@gmail.com>
> wrote:
>
> > So some of my output from an explain analyze here has a line that says
> > this:
> >
> > ex Scan using warranty_order_item_warranty_order_id_idx on
> > warranty_order_item woi_1 (cost=0.57..277.53 rows=6 width=137) (actual
> > time=0.110..0.111 rows=0 loops=1,010,844)
> >
>
> Not my strong suit but, I'm pretty sure that reads: "The index was queried
> 1M+ times and none of those inqueries resulted in a record being found".
> IIUC I'd be wondering why some form of hash join wasn't used...
Except that:
https://www.postgresql.org/docs/current/static/using- explain.html
"... the loops value reports the total number of executions of the node, and
the actual time and ROWS VALUES SHOWN ARE AVERAGES PER-EXECUTION."
I seem to recall a somewhat recent commit that dealt with this. The problem is that with 1M loops a small number of rows returned will be indistinguishable from zero when computed as an average within finite precision.
Seeing entire plans, and not just a single line of one, tends to help too.
David J.
On Wed, Nov 1, 2017 at 1:19 PM, David G. Johnston <david.g.johnston@gmail.com> wrote: > On Wed, Nov 1, 2017 at 11:59 AM, Scott Marlowe <scott.marlowe@gmail.com> > wrote: >> >> So some of my output from an explain analyze here has a line that says >> this: >> >> ex Scan using warranty_order_item_warranty_order_id_idx on >> warranty_order_item woi_1 (cost=0.57..277.53 rows=6 width=137) (actual >> time=0.110..0.111 rows=0 loops=1,010,844) > > > Not my strong suit but, I'm pretty sure that reads: "The index was queried > 1M+ times and none of those inqueries resulted in a record being found". > IIUC I'd be wondering why some form of hash join wasn't used... Thanks I think you've got it. I wish it would fit in a hash but the dataset this query works on is so big that it spills to disk with work_mem=16GB... :( -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general