pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Query too slow
8+ messages / 6 participants
[nested] [flat]

* Query too slow
@ 2003-08-25 15:03 Rhaoni Chiu Pereira <rhaoni@sistemica.info>
  2003-08-25 15:44 ` Re: Query too slow Stephan Szabo <sszabo@megazone.bigpanda.com>
  2003-08-25 16:35 ` Re: Query too slow Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  0 siblings, 2 replies; 8+ messages in thread

From: Rhaoni Chiu Pereira @ 2003-08-25 15:03 UTC (permalink / raw)
  To: pgsql-performance

Hi List,

      I have posted a subjetc on the admin list but I thought that it might fit 
better on this list as follow:

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 :
 
      SELECT /*+  */ 
ftnfco00.estado_cliente , 
ftcofi00.grupo_faturamento , 
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.qtde_duzias,0)),       '+', NVL(ftnfpr00.qtde_duzias,0),       0) ) , 
SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL(ftnfpr00.vlr_liquido,0)), '+', 
NVL(ftnfpr00.vlr_liquido,0), 0) ) , 
ftprod00.tipo_cadastro||ftprod00.codigo_produto||'||'||gsames00.ano_mes , 
ftprod00.descricao_produto||'||'||gsames00.descricao , 
DIVIDE( SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.vlr_liquido,0)), '+', NVL(ftnfpr00.vlr_liquido,0), 0)
*ftnfpr00.margem_comercial ),
                         SUM( DECODE( ftcofi00.atual_fatura, '-', -(NVL
(ftnfpr00.vlr_liquido,0)), '+', NVL(ftnfpr00.vlr_liquido,0), 0)) ) , 
SUM( DECODE( ftcofi00.nf_prodgratis, 'S', NVL(ftnfpr00.qtde_duzias,0), 0 ) ) , 
SUM( DECODE( ftcofi00.nf_prodgratis, 'S', NVL(ftnfpr00.vlr_liquido,0), 0 ) ) 
FROM 
ftprod00 , 
ftnfco00 , 
ftcgma00 , 
ftcgca00 , 
ftspro00 , 
ftclcr00 , 
gsames00 , 
ftcofi00 , 
ftrepr00 , 
gsesta00 , 
ftsupv00 , 
ftgrep00 , 
ftclgr00 , 
ftband00 , 
fttcli00 , 
ftredc00 , 
ftnfpr00 
WHERE 
ftnfco00.emp = 909 AND 
ftnfpr00.fil IN ('101') AND 
ftnfco00.situacao_nf = 'N' AND 
TO_CHAR(ftnfco00.data_emissao,'YYYYMM') >= '200208' AND 
TO_CHAR(ftnfco00.data_emissao,'YYYYMM') <= '200304' AND 
ftcofi00.grupo_faturamento >= '01' AND 
(ftcofi00.atual_fatura IN ('+','-') OR ftcofi00.nf_prodgratis = 'S') AND 
ftcgma00.emp    = ftprod00.emp AND 
ftcgma00.fil    = ftprod00.fil AND 
ftcgma00.codigo = ftprod00.cla_marca AND 
ftcgca00.emp    = ftprod00.emp AND 
ftcgca00.fil    = ftprod00.fil AND 
ftcgca00.codigo = ftprod00.cla_categoria AND 
ftspro00.emp    = ftprod00.emp AND 
ftspro00.fil    = ftprod00.fil AND 
ftspro00.codigo = ftprod00.situacao AND 
ftclcr00.emp           = ftnfco00.emp AND 
ftclcr00.fil           = ftnfco00.empfil AND 
ftclcr00.tipo_cadastro = ftnfco00.tipo_cad_clicre AND 
ftclcr00.codigo        = ftnfco00.cod_cliente AND 
gsames00.ano_mes       = TO_CHAR(ftnfco00.data_emissao,'YYYYMM') AND 
ftcofi00.emp           = ftnfco00.emp AND 
ftcofi00.fil           = ftnfco00.empfil AND 
ftcofi00.codigo_fiscal = ftnfco00.cod_fiscal AND 
ftrepr00.emp           = ftnfco00.emp AND 
ftrepr00.fil           = ftnfco00.empfil AND 
ftrepr00.codigo_repr   = ftnfco00.cod_repres AND 
gsesta00.estado_sigla  = ftnfco00.estado_cliente AND 
ftsupv00.emp               = ftrepr00.emp AND 
ftsupv00.fil               = ftrepr00.fil AND 
ftsupv00.codigo_supervisor = ftrepr00.codigo_supervisor AND 
ftgrep00.emp               = ftrepr00.emp AND 
ftgrep00.fil               = ftrepr00.fil AND 
ftgrep00.codigo_grupo_rep  = ftrepr00.codigo_grupo_rep AND 
ftclgr00.emp               = ftclcr00.emp AND 
ftclgr00.fil               = ftclcr00.fil AND 
ftclgr00.codigo            = ftclcr00.codigo_grupo_cliente AND 
ftband00.emp               = ftclcr00.emp AND 
ftband00.fil               = ftclcr00.fil AND 
ftband00.codigo            = ftclcr00.bandeira_cliente AND 
fttcli00.emp               = ftclcr00.emp AND 
fttcli00.fil               = ftclcr00.fil AND 
fttcli00.cod_tipocliente   = ftclcr00.codigo_tipo_cliente AND 
ftredc00.emp               = ftclcr00.emp AND 
ftredc00.fil               = ftclcr00.fil AND 
ftredc00.tipo_contribuinte = ftclcr00.tipo_contribuinte AND 
ftredc00.codigo_rede       = ftclcr00.codigo_rede AND 
gsesta00.estado_sigla      = ftclcr00.emp_estado AND 
ftnfco00.emp           = ftnfpr00.emp AND 
ftnfco00.fil           = ftnfpr00.fil AND 
ftnfco00.nota_fiscal   = ftnfpr00.nota_fiscal AND 
ftnfco00.serie         = ftnfpr00.serie AND 
ftnfco00.data_emissao  = ftnfpr00.data_emissao AND 
ftprod00.emp           = ftnfpr00.emp AND 
ftprod00.fil           = ftnfpr00.empfil AND 
ftprod00.tipo_cadastro = ftnfpr00.tipo_cad_promat AND 
ftprod00.codigo_produto= ftnfpr00.cod_produto 
GROUP BY 
ftnfco00.estado_cliente , 
ftcofi00.grupo_faturamento , 
ftprod00.tipo_cadastro||ftprod00.codigo_produto||'||'||gsames00.ano_mes , 
ftprod00.descricao_produto||'||'||gsames00.descricao
 

I have created the decode, NVL and DIVIDE functions.... the problem is that the 
where condition makes this query to slow ( about 4 min ) and the same query in 
my Oracle database takes less than 40 seconds. I have tried to isolate the 
problem taking off some  fields and I left justa the two first fields in the 
query ( ftnfco00.estado_cliente , ftcofi00.grupo_faturamento ) and it still 
taking  almost 4 min to return. Does anyone have a hint to give me to make it 
faster ?

    Atached goes a explain analyze return os this query.




Atenciosamente,

Rhaoni Chiu Pereira
Sistêmica Computadores

Visite-nos na Web: http://sistemica.info
Fone/Fax : +55 51 3328 1122

Attachments:

explain.sql (text/plain, 12.6K) download QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- Aggregate (cost=7971.65..7971.68 rows=1 width=833) (actual time=1501529.16..1508066.32 rows=1755 loops=1) -> Group (cost=7971.65..7971.66 rows=1 width=833) (actual time=1501495.07..1502517.99 rows=24111 loops=1) -> Sort (cost=7971.65..7971.65 rows=1 width=833) (actual time=1501495.03..1501563.87 rows=24111 loops=1) Sort Key: ftnfco00.estado_cliente, ftcofi00.grupo_faturamento, ((((ftprod00.tipo_cadastro)::text || (ftprod00.codigo_produto)::text) || '||'::text) || (gsames00.ano_mes)::text), ((ftprod00.descricao_produto || '||'::character varying) || gsames00.descricao) -> Nested Loop (cost=7913.31..7971.64 rows=1 width=833) (actual time=1382281.24..1495748.79 rows=24111loops=1) Join Filter: ("inner".estado_sigla = "outer".estado_cliente) -> Nested Loop (cost=7913.31..7969.90 rows=1 width=827) (actual time=1382271.41..1490702.11 rows=24111 loops=1) Join Filter: (("outer".emp = "inner".emp) AND ("inner".fil = "outer".empfil) AND ("inner".codigo = "outer".bandeira_cliente)) -> Nested Loop (cost=7913.31..7968.87 rows=1 width=795) (actual time=1382179.80..1486492.69 rows=24111 loops=1) Join Filter: (("inner".emp = "outer".emp) AND ("outer".codigo = "inner".cla_categoria)AND ("outer".codigo = "inner".cla_marca) AND ("outer".codigo = "inner".situacao)) -> Nested Loop (cost=7913.31..7954.34 rows=3 width=705) (actual time=1381632.57..1464651.32 rows=241110 loops=1) Join Filter: (("outer".emp = "inner".emp) AND ("inner".fil = "outer".empfil)) -> Nested Loop (cost=7913.31..7941.84 rows=2 width=665) (actual time=1381444.87..1408531.29 rows=241110 loops=1) Join Filter: (("inner".emp = "outer".emp) AND ("outer".fil = "inner".empfil) AND("outer".codigo_grupo_rep = "inner".codigo_grupo_rep)) -> Seq Scan on ftgrep00 (cost=0.00..1.01 rows=1 width=32) (actual time=28.97..28.97 rows=1 loops=1) -> Materialize (cost=7940.79..7940.79 rows=2 width=633) (actual time=1381415.51..1385445.39 rows=241110 loops=1) -> Nested Loop (cost=7913.31..7940.79 rows=2 width=633) (actual time=1348400.96..1379090.93 rows=241110 loops=1) Join Filter: (("inner".emp = "outer".emp) AND ("outer".fil = "inner".empfil) AND ("outer".codigo_supervisor = "inner".codigo_supervisor)) -> Seq Scan on ftsupv00 (cost=0.00..1.01 rows=1 width=32) (actual time=13.92..13.94 rows=1 loops=1) -> Materialize (cost=7939.75..7939.75 rows=2 width=601) (actual time=1348386.88..1357853.77 rows=241110 loops=1) -> Nested Loop (cost=7913.31..7939.75 rows=2 width=601) (actual time=238548.84..1344339.33 rows=241110 loops=1) Join Filter: (("inner".ano_mes)::text = to_char("outer".data_emissao, 'YYYYMM'::text)) -> Merge Join (cost=7913.31..7913.45 rows=2 width=576)(actual time=238524.31..275724.44 rows=241110 loops=1) Merge Cond: (("outer".empfil = "inner".fil) AND ("outer".codigo_tipo_cliente = "inner".cod_tipocliente) AND ("outer".emp = "inner".emp)) -> Sort (cost=7911.79..7911.80 rows=2 width=543)(actual time=238523.48..254284.13 rows=241110 loops=1) Sort Key: ftclcr00.fil, ftclcr00.codigo_tipo_cliente, ftnfpr00.emp -> Nested Loop (cost=7851.29..7911.78 rows=2 width=543) (actual time=80005.55..144755.84 rows=241110 loops=1) Join Filter: (("inner".emp_estado = "outer".estado_cliente) AND ("outer".codigo = "inner".codigo_grupo_cliente)) -> Nested Loop (cost=7851.29..7853.22 rows=10 width=449) (actual time=79960.48..94206.17 rows=241110 loops=1) Join Filter: (("inner".emp = "outer".emp) AND ("outer".fil = "inner".empfil)) -> Seq Scan on ftclgr00 (cost=0.00..1.01 rows=1 width=32) (actual time=12.21..12.21 rows=1 loops=1) -> Materialize (cost=7852.06..7852.06 rows=10 width=417) (actual time=79948.16..82051.01 rows=241110 loops=1) -> Merge Join (cost=7851.29..7852.06 rows=10 width=417) (actual time=65536.22..78515.30 rows=241110 loops=1) Merge Cond: (("outer".empfil = "inner".fil) AND ("outer".cod_repres = "inner".codigo_repr) AND ("outer".emp = "inner".emp)) -> Sort (cost=7840.51..7840.54 rows=10 width=364) (actual time=64203.33..65522.80 rows=241110 loops=1) Sort Key: ftnfco00.empfil, ftnfco00.cod_repres, ftnfpr00.emp -> Nested Loop (cost=7569.51..7840.35 rows=10 width=364) (actual time=2094.38..31469.51 rows=241110 loops=1) Join Filter: (("outer".emp = "inner".emp) AND ("outer".empfil = "inner".fil)) -> Nested Loop (cost=7569.51..7839.10 rows=1 width=337) (actual time=2094.00..21568.08 rows=24111 loops=1) Join Filter: (("outer".emp = "inner".emp) AND ("outer".empfil = "inner".fil)) -> Nested Loop (cost=7569.51..7838.07 rows=1 width=305) (actual time=2086.59..20456.24 rows=24111 loops=1) Join Filter: (("outer".emp = "inner".emp) AND ("outer".empfil = "inner".fil)) -> Nested Loop (cost=7569.51..7837.05 rows=1 width=273) (actual time=2078.21..19368.42 rows=24111 loops=1) -> Merge Join (cost=7569.51..7572.07 rows=44 width=161) (actual time=1991.00..2262.54 rows=7718 loops=1) Merge Cond: (("outer".cod_fiscal = "inner".codigo_fiscal) AND ("outer".empfil = "inner".fil) AND ("outer".emp = "inner".emp)) -> Sort (cost=7520.71..7521.45 rows=295 width=109) (actual time=1958.40..1994.38 rows=9339 loops=1) Sort Key: ftnfco00.cod_fiscal, ftnfco00.empfil, ftnfco00.emp -> Seq Scan on ftnfco00 (cost=0.00..7508.60 rows=295 width=109) (actual time=14.50..1581.81 rows=9339 loops=1) Filter: ((emp = 909::numeric) AND (situacao_nf = 'N'::character varying) AND (to_char(data_emissao, 'YYYYMM'::text) >= '200209'::text) AND (to_char(data_emissao, 'YYYYMM'::text) <= '200304'::text)) -> Sort (cost=48.80..48.98 rows=72 width=52) (actual time=31.86..43.69 rows=7765 loops=1) Sort Key: ftcofi00.codigo_fiscal, ftcofi00.fil, ftcofi00.emp -> Seq Scan on ftcofi00 (cost=0.00..46.58 rows=72 width=52) (actual time=7.62..31.07 rows=75 loops=1) Filter: ((grupo_faturamento >= '01'::character varying) AND ((atual_fatura = '+'::character varying) OR (atual_fatura = '-'::character varying) OR (nf_prodgratis = 'S'::character varying))) -> Index Scan using ftnfpr00_pkey on ftnfpr00 (cost=0.00..5.95 rows=1 width=112) (actual time=1.41..2.14 rows=3 loops=7718) Index Cond: (("outer".emp = ftnfpr00.emp) AND ("outer".fil = ftnfpr00.fil) AND (ftnfpr00.fil = 101::numeric) AND ("outer".nota_fiscal = ftnfpr00.nota_fiscal) AND ("outer".serie = ftnfpr00.serie) AND ("outer".data_emissao = ftnfpr00.data_emissao)) -> Seq Scan on ftcgca00 (cost=0.00..1.01 rows=1 width=32) (actual time=0.01..0.01 rows=1 loops=24111) -> Seq Scan on ftcgma00 (cost=0.00..1.01 rows=1 width=32) (actual time=0.01..0.01 rows=1 loops=24111) -> Seq Scan on ftspro00 (cost=0.00..1.10 rows=10 width=27) (actual time=0.00..0.04 rows=10 loops=24111) -> Sort (cost=10.78..11.05 rows=109 width=53) (actual time=1332.54..1510.69 rows=241175 loops=1) Sort Key: ftrepr00.fil, ftrepr00.codigo_repr, ftrepr00.emp -> Seq Scan on ftrepr00 (cost=0.00..7.09 rows=109 width=53) (actual time=406.63..1331.33 rows=109 loops=1) -> Index Scan using ftclcr01 on ftclcr00 (cost=0.00..5.83 rows=1 width=94) (actual time=0.13..0.14 rows=1 loops=241110) Index Cond: ((ftclcr00.emp = "outer".emp) AND (ftclcr00.fil = "outer".empfil) AND (ftclcr00.tipo_cadastro = "outer".tipo_cad_clicre) AND (ftclcr00.codigo = "ou ter".cod_cliente)) -> Sort (cost=1.52..1.56 rows=17 width=33) (actual time=0.61..225.43 rows=240986 loops=1) Sort Key: fttcli00.fil, fttcli00.cod_tipocliente, fttcli00.emp -> Seq Scan on fttcli00 (cost=0.00..1.17 rows=17 width=33) (actual time=0.12..0.17 rows=17 loops=1) -> Seq Scan on gsames00 (cost=0.00..6.72 rows=372 width=25) (actual time=0.00..1.28 rows=372 loops=241110) -> Index Scan using ftredc00_pkey on ftredc00 (cost=0.00..5.83 rows=1 width=40) (actual time=0.12..0.12 rows=1 loops=241110) Index Cond: ((ftredc00.emp = "outer".emp) AND (ftredc00.fil = "outer".fil) AND (f tredc00.tipo_contribuinte = "outer".tipo_contribuinte) AND (ftredc00.codigo_rede = "outer".codigo_rede)) -> Index Scan using ftprod00_pkey on ftprod00 (cost=0.00..5.74 rows=1 width=90) (actual time=0.06..0.06 rows=1 loops=241110) Index Cond: ((ftprod00.emp = "outer".emp) AND (ftprod00.fil = "outer".empfil) AND (ftprod00.tipo_cadastro = "outer".tipo_cad_promat) AND (ftprod00.codigo_produto = "outer".cod_produto)) -> Seq Scan on ftband00 (cost=0.00..1.01 rows=1 width=32) (actual time=0.01..0.01 rows=1 loops=24111) -> Seq Scan on gsesta00 (cost=0.00..1.33 rows=33 width=6) (actual time=0.01..0.08 rows=33 loops=24111) Total runtime: 1508259.91 msec (74 rows)
^ permalink raw reply [nested|flat] 8+ messages in thread

* Re: Query too slow
  2003-08-25 15:03 Query too slow Rhaoni Chiu Pereira <rhaoni@sistemica.info>
@ 2003-08-25 15:44 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
  2003-08-26 06:49   ` Re: Query too slow Ang Chin Han <angch@bytecraft.com.my>
  1 sibling, 1 reply; 8+ messages in thread

From: Stephan Szabo @ 2003-08-25 15:44 UTC (permalink / raw)
  To: Rhaoni Chiu Pereira <rhaoni@sistemica.info>; +Cc: pgsql-performance

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 240000 in a step where the estimated rows goes to 1.

What does explain analyze give after set enable_nestloop=off;?




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Query too slow
  2003-08-25 15:03 Query too slow Rhaoni Chiu Pereira <rhaoni@sistemica.info>
  2003-08-25 15:44 ` Re: Query too slow Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-08-26 06:49   ` Ang Chin Han <angch@bytecraft.com.my>
  2003-08-26 15:47     ` Re: Query too slow Stephan Szabo <sszabo@megazone.bigpanda.com>
  0 siblings, 1 reply; 8+ messages in thread

From: Ang Chin Han @ 2003-08-26 06:49 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Rhaoni Chiu Pereira <rhaoni@sistemica.info>; pgsql-performance

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

^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Query too slow
  2003-08-25 15:03 Query too slow Rhaoni Chiu Pereira <rhaoni@sistemica.info>
  2003-08-25 15:44 ` Re: Query too slow Stephan Szabo <sszabo@megazone.bigpanda.com>
  2003-08-26 06:49   ` Re: Query too slow Ang Chin Han <angch@bytecraft.com.my>
@ 2003-08-26 15:47     ` Stephan Szabo <sszabo@megazone.bigpanda.com>
  2003-08-26 16:01       ` Re: Query too slow Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Stephan Szabo @ 2003-08-26 15:47 UTC (permalink / raw)
  To: Ang Chin Han <angch@bytecraft.com.my>; +Cc: Rhaoni Chiu Pereira <rhaoni@sistemica.info>; pgsql-performance

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.




^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Query too slow
  2003-08-25 15:03 Query too slow Rhaoni Chiu Pereira <rhaoni@sistemica.info>
  2003-08-25 15:44 ` Re: Query too slow Stephan Szabo <sszabo@megazone.bigpanda.com>
  2003-08-26 06:49   ` Re: Query too slow Ang Chin Han <angch@bytecraft.com.my>
  2003-08-26 15:47     ` Re: Query too slow Stephan Szabo <sszabo@megazone.bigpanda.com>
@ 2003-08-26 16:01       ` Tom Lane <tgl@sss.pgh.pa.us>
  2003-09-05 04:34         ` Re: Query too slow Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Tom Lane @ 2003-08-26 16:01 UTC (permalink / raw)
  To: Stephan Szabo <sszabo@megazone.bigpanda.com>; +Cc: Ang Chin Han <angch@bytecraft.com.my>; Rhaoni Chiu Pereira <rhaoni@sistemica.info>; pgsql-performance

Stephan Szabo <sszabo@megazone.bigpanda.com> 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



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Query too slow
  2003-08-25 15:03 Query too slow Rhaoni Chiu Pereira <rhaoni@sistemica.info>
  2003-08-25 15:44 ` Re: Query too slow Stephan Szabo <sszabo@megazone.bigpanda.com>
  2003-08-26 06:49   ` Re: Query too slow Ang Chin Han <angch@bytecraft.com.my>
  2003-08-26 15:47     ` Re: Query too slow Stephan Szabo <sszabo@megazone.bigpanda.com>
  2003-08-26 16:01       ` Re: Query too slow Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-09-05 04:34         ` Bruce Momjian <pgman@candle.pha.pa.us>
  2003-09-10 20:15           ` Re: Query too slow Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 1 reply; 8+ messages in thread

From: Bruce Momjian @ 2003-09-05 04:34 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Stephan Szabo <sszabo@megazone.bigpanda.com>; Ang Chin Han <angch@bytecraft.com.my>; Rhaoni Chiu Pereira <rhaoni@sistemica.info>; pgsql-performance

Tom Lane wrote:
> Stephan Szabo <sszabo@megazone.bigpanda.com> 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
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Query too slow
  2003-08-25 15:03 Query too slow Rhaoni Chiu Pereira <rhaoni@sistemica.info>
  2003-08-25 15:44 ` Re: Query too slow Stephan Szabo <sszabo@megazone.bigpanda.com>
  2003-08-26 06:49   ` Re: Query too slow Ang Chin Han <angch@bytecraft.com.my>
  2003-08-26 15:47     ` Re: Query too slow Stephan Szabo <sszabo@megazone.bigpanda.com>
  2003-08-26 16:01       ` Re: Query too slow Tom Lane <tgl@sss.pgh.pa.us>
  2003-09-05 04:34         ` Re: Query too slow Bruce Momjian <pgman@candle.pha.pa.us>
@ 2003-09-10 20:15           ` Bruce Momjian <pgman@candle.pha.pa.us>
  0 siblings, 0 replies; 8+ messages in thread

From: Bruce Momjian @ 2003-09-10 20:15 UTC (permalink / raw)
  To: Bruce Momjian <pgman@candle.pha.pa.us>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; Stephan Szabo <sszabo@megazone.bigpanda.com>; Ang Chin Han <angch@bytecraft.com.my>; Rhaoni Chiu Pereira <rhaoni@sistemica.info>; pgsql-performance

Bruce Momjian wrote:
> Tom Lane wrote:
> > Stephan Szabo <sszabo@megazone.bigpanda.com> 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
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073



^ permalink  raw  reply  [nested|flat] 8+ messages in thread

* Re: Query too slow
  2003-08-25 15:03 Query too slow Rhaoni Chiu Pereira <rhaoni@sistemica.info>
@ 2003-08-25 16:35 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
  1 sibling, 0 replies; 8+ messages in thread

From: Shridhar Daithankar @ 2003-08-25 16:35 UTC (permalink / raw)
  To: pgsql-performance

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 240000 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.




^ permalink  raw  reply  [nested|flat] 8+ messages in thread


end of thread, other threads:[~2003-09-10 20:15 UTC | newest]

Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-08-25 15:03 Query too slow Rhaoni Chiu Pereira <rhaoni@sistemica.info>
2003-08-25 15:44 ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-26 06:49   ` Ang Chin Han <angch@bytecraft.com.my>
2003-08-26 15:47     ` Stephan Szabo <sszabo@megazone.bigpanda.com>
2003-08-26 16:01       ` Tom Lane <tgl@sss.pgh.pa.us>
2003-09-05 04:34         ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-09-10 20:15           ` Bruce Momjian <pgman@candle.pha.pa.us>
2003-08-25 16:35 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox