Re: [PERFORM] Query too slow
Bruce Momjian wrote: > Tom Lane wrote: > > Stephan Szabo <[EMAIL PROTECTED]> writes: > > > On Tue, 26 Aug 2003, Ang Chin Han wrote: > > >> Veering aside a bit, since we usually pinpoint performance problems by > > >> looking at EXPLAIN ANALYZE's differences between the planner's > > >> estimation and actual execution's stats, what's involved in parsing the > > >> EXPLAIN ANALYZE results, and highlighting the places where they are way > > >> different? Bold, underline, or put some asterisks in front of those steps. > > > > > The hardest part is determining where it matters I think. You can use the > > > row counts as the base for that, but going from 1 row to 50 is not > > > necessarily going to be an issue, but it might be if a nested loop is > > > chosen. > > > > We've been chatting about this idea among the Red Hat group. The RHDB > > Visual Explain tool (get it at http://sources.redhat.com/rhdb/) already > > computes the percent of total runtime represented by each plan node. > > It seems like we could highlight nodes based on a large difference > > between estimated and actual percentage, or just highlight the nodes > > that are more than X percent of the runtime. > > Is there a TODO here? Perhaps: > > o Have EXPLAIN ANALYZE highlight poor optimizer estimates No one commented, so I had to guess --- I added it. -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Query too slow
Tom Lane wrote: > Stephan Szabo <[EMAIL PROTECTED]> writes: > > On Tue, 26 Aug 2003, Ang Chin Han wrote: > >> Veering aside a bit, since we usually pinpoint performance problems by > >> looking at EXPLAIN ANALYZE's differences between the planner's > >> estimation and actual execution's stats, what's involved in parsing the > >> EXPLAIN ANALYZE results, and highlighting the places where they are way > >> different? Bold, underline, or put some asterisks in front of those steps. > > > The hardest part is determining where it matters I think. You can use the > > row counts as the base for that, but going from 1 row to 50 is not > > necessarily going to be an issue, but it might be if a nested loop is > > chosen. > > We've been chatting about this idea among the Red Hat group. The RHDB > Visual Explain tool (get it at http://sources.redhat.com/rhdb/) already > computes the percent of total runtime represented by each plan node. > It seems like we could highlight nodes based on a large difference > between estimated and actual percentage, or just highlight the nodes > that are more than X percent of the runtime. Is there a TODO here? Perhaps: o Have EXPLAIN ANALYZE highlight poor optimizer estimates -- Bruce Momjian| http://candle.pha.pa.us [EMAIL PROTECTED] | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Query too slow
Stephan Szabo <[EMAIL PROTECTED]> writes: > On Tue, 26 Aug 2003, Ang Chin Han wrote: >> Veering aside a bit, since we usually pinpoint performance problems by >> looking at EXPLAIN ANALYZE's differences between the planner's >> estimation and actual execution's stats, what's involved in parsing the >> EXPLAIN ANALYZE results, and highlighting the places where they are way >> different? Bold, underline, or put some asterisks in front of those steps. > The hardest part is determining where it matters I think. You can use the > row counts as the base for that, but going from 1 row to 50 is not > necessarily going to be an issue, but it might be if a nested loop is > chosen. We've been chatting about this idea among the Red Hat group. The RHDB Visual Explain tool (get it at http://sources.redhat.com/rhdb/) already computes the percent of total runtime represented by each plan node. It seems like we could highlight nodes based on a large difference between estimated and actual percentage, or just highlight the nodes that are more than X percent of the runtime. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [PERFORM] Query too slow
On Tue, 26 Aug 2003, Ang Chin Han wrote: > Stephan Szabo wrote: > > > Looking at the explain: > > Veering aside a bit, since we usually pinpoint performance problems by > looking at EXPLAIN ANALYZE's differences between the planner's > estimation and actual execution's stats, what's involved in parsing the > EXPLAIN ANALYZE results, and highlighting the places where they are way > different? Bold, underline, or put some asterisks in front of those steps. The hardest part is determining where it matters I think. You can use the row counts as the base for that, but going from 1 row to 50 is not necessarily going to be an issue, but it might be if a nested loop is chosen. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [PERFORM] Query too slow
Stephan Szabo wrote: Looking at the explain: Veering aside a bit, since we usually pinpoint performance problems by looking at EXPLAIN ANALYZE's differences between the planner's estimation and actual execution's stats, what's involved in parsing the EXPLAIN ANALYZE results, and highlighting the places where they are way different? Bold, underline, or put some asterisks in front of those steps. Makes looking at big EXPLAIN ANALYZE trees much easier. -- Linux homer 2.4.18-14 #1 Wed Sep 4 13:35:50 EDT 2002 i686 i686 i386 GNU/Linux 2:30pm up 243 days, 5:48, 8 users, load average: 5.52, 5.29, 5.10 pgp0.pgp Description: PGP signature
Re: [PERFORM] Query too slow
On 25 Aug 2003 at 8:44, Stephan Szabo wrote: > On Mon, 25 Aug 2003, Rhaoni Chiu Pereira wrote: > > > Hi List, > > > > As I said before, I'm not a DBA " yet" , but I'm learning ... and I > > already have a PostgreSQL running, so I have to ask some help... > > I got a SQL as folows : > > ... > > Looking at the explain: > > It's choosing lots of nested loops because it's expecting a small number > of rows to be returned at each step but in reality there are alot of rows > so that's may not really be a good choice. > > For example the scan of ftnfco00 is expected to return 295 rows but > actually returns 9339, and it looks like it's not estimating the number of > matches between the tables very well either since the real count gets up > to 24 in a step where the estimated rows goes to 1. > > What does explain analyze give after set enable_nestloop=off;? In addition to that if it is getting the stats wrong, does running vacuum analyze help? If stats are updated, it should pick up proper plans, right? Bye Shridhar -- Flon's Law: There is not now, and never will be, a language in which it is the least bit difficult to write bad programs. ---(end of broadcast)--- TIP 8: explain analyze is your friend
Re: [PERFORM] Query too slow
On Mon, 25 Aug 2003, Rhaoni Chiu Pereira wrote: > Hi List, > > As I said before, I'm not a DBA " yet" , but I'm learning ... and I > already have a PostgreSQL running, so I have to ask some help... > I got a SQL as folows : ... Looking at the explain: It's choosing lots of nested loops because it's expecting a small number of rows to be returned at each step but in reality there are alot of rows so that's may not really be a good choice. For example the scan of ftnfco00 is expected to return 295 rows but actually returns 9339, and it looks like it's not estimating the number of matches between the tables very well either since the real count gets up to 24 in a step where the estimated rows goes to 1. What does explain analyze give after set enable_nestloop=off;? ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
