pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery 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