Postgres Pro
Downloads
Home   >   mailing lists

Thread: [EXPLAIN] Nested loops

[EXPLAIN] Nested loops

From
Reg Me Please
Date:
Hi.

For an INNER JOINed query, EXPLAIN says that a "nested loop" is responsible
for the big part of the time needed to run.

The 2 tables JOINed are:

T1: multi-million rows
T2: few dozens rows

The join is though a single column in both sides and it's NOT a PK in either
table. But I have indexes in both T1 and T2 for that column.

I've read in the "Explaining EXPLAIN" by Rober Treat
(at http://wiki.postgresql.org/wiki/Image:OSCON2005-ExplainingExplain.sxi)
that this nested loop can be slow because of lacking of indexes.

Is there any hint to try to speed that query up?

As of now, only a REINDEX can help thanks to caching, I presume.
But the EXPLAIN still says there's a slow nested loop.

--
Fahrbahn ist ein graues Band
weisse Streifen, grüner Rand

Re: [EXPLAIN] Nested loops

From
"Victor Nawothnig"
Date:
Could you provide the output of EXPLAIN ANALYZE with your query?

On Fri, Jan 9, 2009 at 7:06 PM, Reg Me Please <regmeplease@gmail.com> wrote:
> Hi.
>
> For an INNER JOINed query, EXPLAIN says that a "nested loop" is responsible
> for the big part of the time needed to run.
>
> The 2 tables JOINed are:
>
> T1: multi-million rows
> T2: few dozens rows
>
> The join is though a single column in both sides and it's NOT a PK in either
> table. But I have indexes in both T1 and T2 for that column.
>
> I've read in the "Explaining EXPLAIN" by Rober Treat
> (at http://wiki.postgresql.org/wiki/Image:OSCON2005-ExplainingExplain.sxi)
> that this nested loop can be slow because of lacking of indexes.
>
> Is there any hint to try to speed that query up?
>
> As of now, only a REINDEX can help thanks to caching, I presume.
> But the EXPLAIN still says there's a slow nested loop.
>
> --
> Fahrbahn ist ein graues Band
> weisse Streifen, grüner Rand
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

Re: [EXPLAIN] Nested loops

From
Reg Me Please
Date:
Here it comes:

 Aggregate  (cost=227.59..227.61 rows=1 width=8)
   ->  Nested Loop  (cost=0.00..227.34 rows=49 width=8)
         ->  Seq Scan on T2  (cost=0.00..1.07 rows=6 width=4)
               Filter: (fld1 = 'VEND'::text)
         ->  Index Scan using i_T1_partial on T1  (cost=0.00..37.61 rows=8
width=8)
               Index Cond: ((T1.prod_id = 42) AND (T1.fk1 = T2.fk1))

On Friday 09 January 2009 19:22:28 Victor Nawothnig wrote:
> Could you provide the output of EXPLAIN ANALYZE with your query?
>
> On Fri, Jan 9, 2009 at 7:06 PM, Reg Me Please <regmeplease@gmail.com> wrote:
> > Hi.
> >
> > For an INNER JOINed query, EXPLAIN says that a "nested loop" is
> > responsible for the big part of the time needed to run.
> >
> > The 2 tables JOINed are:
> >
> > T1: multi-million rows
> > T2: few dozens rows
> >
> > The join is though a single column in both sides and it's NOT a PK in
> > either table. But I have indexes in both T1 and T2 for that column.
> >
> > I've read in the "Explaining EXPLAIN" by Rober Treat
> > (at
> > http://wiki.postgresql.org/wiki/Image:OSCON2005-ExplainingExplain.sxi)
> > that this nested loop can be slow because of lacking of indexes.
> >
> > Is there any hint to try to speed that query up?
> >
> > As of now, only a REINDEX can help thanks to caching, I presume.
> > But the EXPLAIN still says there's a slow nested loop.
> >
> > --
> > Fahrbahn ist ein graues Band
> > weisse Streifen, grüner Rand
> >
> > --
> > Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> > To make changes to your subscription:
> > http://www.postgresql.org/mailpref/pgsql-general



--
--
Fahrbahn ist ein graues Band
weisse Streifen, grüner Rand

Re: [EXPLAIN] Nested loops

From
Tom Lane
Date:
Reg Me Please <regmeplease@gmail.com> writes:
>  Aggregate  (cost=227.59..227.61 rows=1 width=8)
>    ->  Nested Loop  (cost=0.00..227.34 rows=49 width=8)
>          ->  Seq Scan on T2  (cost=0.00..1.07 rows=6 width=4)
>                Filter: (fld1 = 'VEND'::text)
>          ->  Index Scan using i_T1_partial on T1  (cost=0.00..37.61 rows=8
> width=8)
>                Index Cond: ((T1.prod_id = 42) AND (T1.fk1 = T2.fk1))

If those rowcount estimates are accurate, then this is a perfectly good
plan; in fact probably the best you could hope for.

            regards, tom lane

Re: [EXPLAIN] Nested loops

From
Reg Me Please
Date:
On Friday 09 January 2009 20:00:36 Thomas Pundt wrote:
> Reg Me Please wrote:
> > Here it comes:
> >
> >  Aggregate  (cost=227.59..227.61 rows=1 width=8)
> >    ->  Nested Loop  (cost=0.00..227.34 rows=49 width=8)
> >          ->  Seq Scan on T2  (cost=0.00..1.07 rows=6 width=4)
> >                Filter: (fld1 = 'VEND'::text)
> >          ->  Index Scan using i_T1_partial on T1  (cost=0.00..37.61
> > rows=8 width=8)
> >                Index Cond: ((T1.prod_id = 42) AND (T1.fk1 = T2.fk1))
>
> That's the EXPLAIN output, _not_ EXPLAIN ANALYZE as requested.
>
> Probably statistics aren't up-to-date?
>
> Thomas

Correct!
I changed a value in the WHERE condition to avoid some caching (maybe useless
in this case).

QUERY PLAN

-------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Aggregate  (cost=227.59..227.61 rows=1 width=8) (actual time=151.722..151.723
rows=1 loops=1)
   ->  Nested Loop  (cost=0.00..227.34 rows=49 width=8)  (actual
time=25.157..151.507 rows=44 loops=1)
         ->  Seq Scan on T2  (cost=0.00..1.07 rows=6 width=4) (actual
time=0.015..0.032 rows=6 loops=1)
               Filter: (fld1 = 'VEND'::text)
         ->  Index Scan using i_T1_partial on T1  (cost=0.00..37.61 rows=8
width=8) (actual time=5.435..25.226 rows=7 loops=6)
               Index Cond: ((T1.prod_id = 4242) AND (T1.fk1 = T2.fk1))
 Total runtime: 151.818 ms
(7 rows)


--
Fahrbahn ist ein graues Band
weisse Streifen, grüner Rand

Re: [EXPLAIN] Nested loops

From
Reg Me Please
Date:
On Friday 09 January 2009 20:00:57 Tom Lane wrote:
> Reg Me Please <regmeplease@gmail.com> writes:
> >  Aggregate  (cost=227.59..227.61 rows=1 width=8)
> >    ->  Nested Loop  (cost=0.00..227.34 rows=49 width=8)
> >          ->  Seq Scan on T2  (cost=0.00..1.07 rows=6 width=4)
> >                Filter: (fld1 = 'VEND'::text)
> >          ->  Index Scan using i_T1_partial on T1  (cost=0.00..37.61
> > rows=8 width=8)
> >                Index Cond: ((T1.prod_id = 42) AND (T1.fk1 = T2.fk1))
>
> If those rowcount estimates are accurate, then this is a perfectly good
> plan; in fact probably the best you could hope for.
>
>             regards, tom lane

Rowcounts seems to be quite accurate as the tables get "VACUUM FULL ANALYZE"d
regularly.
This query plan comes from index optimization.
It is the cost estimate for the nested loop that scares me a little.

As of now only file system caching seems to help the timing (by a factor 10)
but this in turn is a transitory effect as I have little control over FS
cache.

--
Fahrbahn ist ein graues Band
weisse Streifen, grüner Rand

Re: [EXPLAIN] Nested loops

From
Thomas Pundt
Date:
Reg Me Please wrote:
> Here it comes:
>
>  Aggregate  (cost=227.59..227.61 rows=1 width=8)
>    ->  Nested Loop  (cost=0.00..227.34 rows=49 width=8)
>          ->  Seq Scan on T2  (cost=0.00..1.07 rows=6 width=4)
>                Filter: (fld1 = 'VEND'::text)
>          ->  Index Scan using i_T1_partial on T1  (cost=0.00..37.61 rows=8
> width=8)
>                Index Cond: ((T1.prod_id = 42) AND (T1.fk1 = T2.fk1))

That's the EXPLAIN output, _not_ EXPLAIN ANALYZE as requested.

Probably statistics aren't up-to-date?

Thomas