Обсуждение: [EXPLAIN] Nested loops
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
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 >
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
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
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
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
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