Обсуждение: Learning to read explain
Any good sources on how to properly interpret explain? For instance I started out with something like: Hash Join (cost=20279.05..128002.12 rows=1064 width=78) -> Seq Scan on yhearn yhe (cost=0.00..23058.65 rows=1175565 width=38) -> Hash (cost=18659.62..18659.62 rows=156970 width=40) -> Seq Scan on hearn he (cost=0.00..18659.62 rows=156970 width=40) After looking at my tables and what would be my most restrictive conditions I got down to: Nested Loop (cost=0.00..65999.17 rows=21 width=78) -> Seq Scan on yhearn yhe (cost=0.00..28936.47 rows=7655 width=38) -> Index Scan using he_horse on hearn he (cost=0.00..4.82 rows=1 width=40) But it would have helped if I better understood what is the "cost". Also what is the "Hash" on the first version of my not-optimized query.
I too would like help on this subject. Here is the little bit I know (or think I know): The explains are read backwards. Start at the bottom and read up. The Hash thing means a hash table is being created "on the fly" to do a join or meet some WHERE criteria. You will also see Sort appear when you ask for DISTINCT or GROUP BY. Again, the sort is done "on the fly" in order to fulfill your request. --- Presumed knowledge ends here --- --rob ----- Original Message ----- From: "Francisco Reyes" <lists@natserv.com> To: "Pgsql Novice" <pgsql-novice@postgresql.org> Sent: Thursday, November 29, 2001 6:03 PM Subject: Learning to read explain > Any good sources on how to properly interpret explain? > > For instance I started out with something like: > Hash Join (cost=20279.05..128002.12 rows=1064 width=78) > -> Seq Scan on yhearn yhe (cost=0.00..23058.65 rows=1175565 width=38) > -> Hash (cost=18659.62..18659.62 rows=156970 width=40) > -> Seq Scan on hearn he (cost=0.00..18659.62 rows=156970 width=40) > > > After looking at my tables and what would be my most restrictive > conditions I got down to: > Nested Loop (cost=0.00..65999.17 rows=21 width=78) > -> Seq Scan on yhearn yhe (cost=0.00..28936.47 rows=7655 width=38) > -> Index Scan using he_horse on hearn he (cost=0.00..4.82 rows=1 width=40) > > But it would have helped if I better understood what is the "cost". > Also what is the "Hash" on the first version of my not-optimized query. > >
On Fri, 30 Nov 2001, Rob Arnold wrote: > I too would like help on this subject. It seems a common issue for newbies, yet I don't see any docs on the subject. Maybe there are, I just don't know where they are. > Here is the little bit I know (or think I know): Thanks for the info. If I do eventually get to understand "explain", I may try and write something up. One of the most common questions has to be "why is such query so slow". Many times is just a matter of an index not been used or not been present at atll. Sometimes, however, even when there is an index the optimizer doesn't use the index and the user asks itself why. I think that "explain" would have the answer if one knew how to read it.
Francisco Reyes <lists@natserv.com> writes: > It seems a common issue for newbies, yet I don't see any docs on the > subject. Maybe there are, I just don't know where they are. http://www.ca.postgresql.org/users-lounge/docs/7.1/postgres/performance-tips.html There is a slightly improved (IMHO) writeup in the 7.2 docs, http://candle.pha.pa.us/main/writings/pgsql/sgml/performance-tips.html but this also talks about features like EXPLAIN ANALYZE that don't exist in 7.1 ... regards, tom lane