pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedbad plan
19+ messages / 9 participants
[nested] [flat]
* bad plan
@ 2005-03-08 10:32 Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 11:39 ` Re: bad plan Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 19+ messages in thread
From: Gaetano Mendola @ 2005-03-08 10:32 UTC (permalink / raw)
To: pgsql-performance
I posted this on hackers, but I had to post it here.
===================================================================================================================================
Hi all,
running a 7.4.5 engine, I'm facing this bad plan:
empdb=# explain analyze SELECT name,url,descr,request_status,url_status,size_mb,estimated_start,request_time_stamp
empdb-# FROM v_sc_user_request
empdb-# WHERE
empdb-# login = 'babinow1'
empdb-# LIMIT 10 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=1716.38..1716.39 rows=1 width=232) (actual time=52847.239..52847.322 rows=10 loops=1)
-> Subquery Scan v_sc_user_request (cost=1716.38..1716.39 rows=1 width=232) (actual time=52847.234..52847.301 rows=10 loops=1)
-> Sort (cost=1716.38..1716.39 rows=1 width=201) (actual time=52847.219..52847.227 rows=10 loops=1)
Sort Key: sr.id_sat_request
-> Nested Loop Left Join (cost=1478.82..1716.37 rows=1 width=201) (actual time=3254.483..52847.064 rows=31 loops=1)
Join Filter: ("outer".id_package = "inner".id_package)
-> Nested Loop (cost=493.09..691.55 rows=1 width=193) (actual time=347.665..940.582 rows=31 loops=1)
-> Nested Loop (cost=493.09..688.49 rows=1 width=40) (actual time=331.446..505.628 rows=31 loops=1)
Join Filter: ("inner".id_user = "outer".id_user)
-> Index Scan using user_login_login_key on user_login ul (cost=0.00..4.00 rows=2 width=16) (actual time=12.065..12.071 rows=1 loops=1)
Index Cond: ((login)::text = 'babinow1'::text)
-> Materialize (cost=493.09..531.37 rows=7656 width=28) (actual time=167.654..481.813 rows=8363 loops=1)
-> Seq Scan on sat_request sr (cost=0.00..493.09 rows=7656 width=28) (actual time=167.644..467.344 rows=8363 loops=1)
Filter: (request_time > (now() - '1 mon'::interval))
-> Index Scan using url_pkey on url u (cost=0.00..3.05 rows=1 width=161) (actual time=13.994..14.000 rows=1 loops=31)
Index Cond: ("outer".id_url = u.id_url)
-> Subquery Scan vsp (cost=985.73..1016.53 rows=1103 width=12) (actual time=25.328..1668.754 rows=493 loops=31)
-> Merge Join (cost=985.73..1011.01 rows=1103 width=130) (actual time=25.321..1666.666 rows=493 loops=31)
Merge Cond: ("outer".id_program = "inner".id_program)
-> Sort (cost=20.74..20.97 rows=93 width=19) (actual time=0.385..0.431 rows=47 loops=31)
Sort Key: programs.id_program
-> Seq Scan on programs (cost=0.00..17.70 rows=93 width=19) (actual time=0.022..11.709 rows=48 loops=1)
Filter: (id_program <> 0)
-> Sort (cost=964.99..967.75 rows=1102 width=115) (actual time=14.592..15.218 rows=493 loops=31)
Sort Key: sequences.id_program
-> Merge Join (cost=696.16..909.31 rows=1102 width=115) (actual time=79.717..451.495 rows=493 loops=1)
Merge Cond: ("outer".id_package = "inner".id_package)
-> Merge Left Join (cost=0.00..186.59 rows=1229 width=103) (actual time=0.101..366.854 rows=1247 loops=1)
Merge Cond: ("outer".id_package = "inner".id_package)
-> Index Scan using packages_pkey on packages p (cost=0.00..131.04 rows=1229 width=103) (actual time=0.048..163.503 rows=1247 loops=1)
-> Index Scan using package_security_id_package_key on package_security ps (cost=0.00..46.83 rows=855 width=4) (actual time=0.022..178.599 rows=879 loops=1)
-> Sort (cost=696.16..705.69 rows=3812 width=16) (actual time=79.582..79.968 rows=493 loops=1)
Sort Key: sequences.id_package
-> Seq Scan on sequences (cost=0.00..469.42 rows=3812 width=16) (actual time=0.012..78.863 rows=493 loops=1)
Filter: (estimated_start IS NOT NULL)
Total runtime: 52878.516 ms
(36 rows)
Disabling the nestloop then the execution time become more affordable:
empdb=# set enable_nestloop = false;
SET
empdb=# explain analyze SELECT name,url,descr,request_status,url_status,size_mb,estimated_start,request_time_stamp
empdb-# FROM v_sc_user_request
empdb-# WHERE
empdb-# login = 'babinow1'
empdb-# LIMIT 10 ;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=4467.64..4467.65 rows=1 width=232) (actual time=7091.233..7091.289 rows=10 loops=1)
-> Subquery Scan v_sc_user_request (cost=4467.64..4467.65 rows=1 width=232) (actual time=7091.228..7091.272 rows=10 loops=1)
-> Sort (cost=4467.64..4467.64 rows=1 width=201) (actual time=7091.216..7091.221 rows=10 loops=1)
Sort Key: sr.id_sat_request
-> Merge Left Join (cost=4462.07..4467.63 rows=1 width=201) (actual time=6377.732..7091.067 rows=31 loops=1)
Merge Cond: ("outer".id_package = "inner".id_package)
-> Sort (cost=3389.81..3389.81 rows=1 width=193) (actual time=1338.759..1338.814 rows=31 loops=1)
Sort Key: sr.id_package
-> Merge Join (cost=3285.05..3389.80 rows=1 width=193) (actual time=1318.877..1338.651 rows=31 loops=1)
Merge Cond: ("outer".id_url = "inner".id_url)
-> Sort (cost=1029.26..1029.26 rows=1 width=40) (actual time=703.085..703.113 rows=31 loops=1)
Sort Key: sr.id_url
-> Merge Join (cost=991.00..1029.25 rows=1 width=40) (actual time=702.740..702.984 rows=31 loops=1)
Merge Cond: ("outer".id_user = "inner".id_user)
-> Sort (cost=986.99..1006.13 rows=7656 width=28) (actual time=648.559..655.302 rows=8041 loops=1)
Sort Key: sr.id_user
-> Seq Scan on sat_request sr (cost=0.00..493.09 rows=7656 width=28) (actual time=201.968..614.631 rows=8363 loops=1)
Filter: (request_time > (now() - '1 mon'::interval))
-> Sort (cost=4.01..4.02 rows=2 width=16) (actual time=35.252..35.282 rows=1 loops=1)
Sort Key: ul.id_user
-> Index Scan using user_login_login_key on user_login ul (cost=0.00..4.00 rows=2 width=16) (actual time=35.214..35.221 rows=1 loops=1)
Index Cond: ((login)::text = 'babinow1'::text)
-> Sort (cost=2255.79..2308.95 rows=21264 width=161) (actual time=587.664..602.490 rows=21250 loops=1)
Sort Key: u.id_url
-> Seq Scan on url u (cost=0.00..727.32 rows=21264 width=161) (actual time=0.026..418.586 rows=21264 loops=1)
-> Sort (cost=1072.27..1075.03 rows=1103 width=12) (actual time=5015.761..5016.092 rows=493 loops=1)
Sort Key: vsp.id_package
-> Subquery Scan vsp (cost=985.73..1016.53 rows=1103 width=12) (actual time=898.876..5014.570 rows=494 loops=1)
-> Merge Join (cost=985.73..1011.01 rows=1103 width=130) (actual time=898.869..5011.954 rows=494 loops=1)
Merge Cond: ("outer".id_program = "inner".id_program)
-> Sort (cost=20.74..20.97 rows=93 width=19) (actual time=29.669..29.708 rows=47 loops=1)
Sort Key: programs.id_program
-> Seq Scan on programs (cost=0.00..17.70 rows=93 width=19) (actual time=0.035..29.525 rows=48 loops=1)
Filter: (id_program <> 0)
-> Sort (cost=964.99..967.75 rows=1102 width=115) (actual time=868.619..869.286 rows=494 loops=1)
Sort Key: sequences.id_program
-> Merge Join (cost=696.16..909.31 rows=1102 width=115) (actual time=44.820..867.649 rows=494 loops=1)
Merge Cond: ("outer".id_package = "inner".id_package)
-> Merge Left Join (cost=0.00..186.59 rows=1229 width=103) (actual time=19.563..835.352 rows=1248 loops=1)
Merge Cond: ("outer".id_package = "inner".id_package)
-> Index Scan using packages_pkey on packages p (cost=0.00..131.04 rows=1229 width=103) (actual time=12.796..457.520 rows=1248 loops=1)
-> Index Scan using package_security_id_package_key on package_security ps (cost=0.00..46.83 rows=855 width=4) (actual time=6.703..283.944 rows=879 loops=1)
-> Sort (cost=696.16..705.69 rows=3812 width=16) (actual time=25.222..25.705 rows=494 loops=1)
Sort Key: sequences.id_package
-> Seq Scan on sequences (cost=0.00..469.42 rows=3812 width=16) (actual time=0.017..24.412 rows=494 loops=1)
Filter: (estimated_start IS NOT NULL)
Total runtime: 7104.946 ms
(47 rows)
May I know wich parameter may I tune in order to avoid to "disable" the nested loop ?
===================================================================================================================================
I tried to reduce the runtime cost adding a new column on sat_request ( expired boolean ) in order to
have a better row extimation ( I used a partial index ) but nothing changed.
I finally was able to reduce the cost putting ( just for this query ):
set cpu_tuple_cost = 0.07
is it a resonable value ?
empdb=# set cpu_tuple_cost = 0.07;
SET
empdb=# explain analyze select *
empdb-# from v_sc_user_request
empdb-# where login = 'babinow1';
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan v_sc_user_request (cost=1978.23..1978.30 rows=1 width=364) (actual time=1612.719..1613.064 rows=31 loops=1)
-> Sort (cost=1978.23..1978.23 rows=1 width=201) (actual time=1612.700..1612.728 rows=31 loops=1)
Sort Key: sr.id_sat_request
-> Merge Left Join (cost=1974.05..1978.22 rows=1 width=201) (actual time=1537.343..1612.565 rows=31 loops=1)
Merge Cond: ("outer".id_package = "inner".id_package)
-> Sort (cost=887.92..887.93 rows=1 width=193) (actual time=475.924..476.020 rows=31 loops=1)
Sort Key: sr.id_package
-> Nested Loop (cost=4.07..887.91 rows=1 width=193) (actual time=145.782..475.851 rows=31 loops=1)
-> Hash Join (cost=4.07..884.65 rows=1 width=40) (actual time=139.816..464.678 rows=31 loops=1)
Hash Cond: ("outer".id_user = "inner".id_user)
-> Index Scan using idx_sat_request_expired on sat_request sr (cost=0.00..838.69 rows=8363 width=28) (actual time=19.696..443.702 rows=8460 loops=1)
Index Cond: (expired = false)
-> Hash (cost=4.07..4.07 rows=2 width=16) (actual time=11.779..11.779 rows=0 loops=1)
-> Index Scan using user_login_login_key on user_login ul (cost=0.00..4.07 rows=2 width=16) (actual time=11.725..11.732 rows=1 loops=1)
Index Cond: ((login)::text = 'babinow1'::text)
-> Index Scan using url_pkey on url u (cost=0.00..3.19 rows=1 width=161) (actual time=0.345..0.347 rows=1 loops=31)
Index Cond: ("outer".id_url = u.id_url)
-> Sort (cost=1086.13..1088.16 rows=813 width=12) (actual time=1060.374..1060.622 rows=390 loops=1)
Sort Key: vsp.id_package
-> Subquery Scan vsp (cost=676.18..1046.83 rows=813 width=12) (actual time=625.645..1059.388 rows=480 loops=1)
-> Hash Join (cost=676.18..989.92 rows=813 width=131) (actual time=625.637..1057.105 rows=480 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Hash Left Join (cost=79.67..302.87 rows=1341 width=104) (actual time=4.336..18.549 rows=1342 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Seq Scan on packages p (cost=0.00..145.87 rows=1341 width=104) (actual time=0.007..3.357 rows=1342 loops=1)
-> Hash (cost=77.27..77.27 rows=961 width=4) (actual time=3.685..3.685 rows=0 loops=1)
-> Seq Scan on package_security ps (cost=0.00..77.27 rows=961 width=4) (actual time=0.016..2.175 rows=974 loops=1)
-> Hash (cost=594.48..594.48 rows=813 width=31) (actual time=620.397..620.397 rows=0 loops=1)
-> Hash Join (cost=20.60..594.48 rows=813 width=31) (actual time=38.307..619.406 rows=480 loops=1)
Hash Cond: ("outer".id_program = "inner".id_program)
-> Seq Scan on sequences (cost=0.00..512.82 rows=830 width=16) (actual time=16.858..595.262 rows=480 loops=1)
Filter: (estimated_start IS NOT NULL)
-> Hash (cost=20.48..20.48 rows=47 width=19) (actual time=21.093..21.093 rows=0 loops=1)
-> Seq Scan on programs (cost=0.00..20.48 rows=47 width=19) (actual time=9.369..20.980 rows=48 loops=1)
Filter: (id_program <> 0)
Total runtime: 1614.123 ms
(36 rows)
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: bad plan
2005-03-08 10:32 bad plan Gaetano Mendola <mendola@bigfoot.com>
@ 2005-03-08 11:39 ` Richard Huxton <dev@archonet.com>
2005-03-08 12:22 ` Re: bad plan Gaetano Mendola <mendola@bigfoot.com>
0 siblings, 1 reply; 19+ messages in thread
From: Richard Huxton @ 2005-03-08 11:39 UTC (permalink / raw)
To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: pgsql-performance
Gaetano Mendola wrote:
> running a 7.4.5 engine, I'm facing this bad plan:
>
> empdb=# explain analyze SELECT name,url,descr,request_status,url_status,size_mb,estimated_start,request_time_stamp
> empdb-# FROM v_sc_user_request
> empdb-# WHERE
> empdb-# login = 'babinow1'
> empdb-# LIMIT 10 ;
> -> Subquery Scan vsp (cost=985.73..1016.53 rows=1103 width=12) (actual time=25.328..1668.754 rows=493 loops=31)
> -> Merge Join (cost=985.73..1011.01 rows=1103 width=130) (actual time=25.321..1666.666 rows=493 loops=31)
> Merge Cond: ("outer".id_program = "inner".id_program)
The problem to address is in this subquery. That's a total of 31 x
(1668.754 - 25.328) = 50seconds (about).
Since your query is so simple, I'm guessing v_sc_user_request is a view.
Can you provide the definition?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: bad plan
2005-03-08 10:32 bad plan Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 11:39 ` Re: bad plan Richard Huxton <dev@archonet.com>
@ 2005-03-08 12:22 ` Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 13:20 ` Re: bad plan Richard Huxton <dev@archonet.com>
2005-03-08 20:01 ` Re: bad plan Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 19+ messages in thread
From: Gaetano Mendola @ 2005-03-08 12:22 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Gaetano Mendola <mendola@bigfoot.com>; pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Richard Huxton wrote:
> Gaetano Mendola wrote:
>
>> running a 7.4.5 engine, I'm facing this bad plan:
>>
>> empdb=# explain analyze SELECT
>> name,url,descr,request_status,url_status,size_mb,estimated_start,request_time_stamp
>>
>> empdb-# FROM v_sc_user_request
>> empdb-# WHERE
>> empdb-# login = 'babinow1'
>> empdb-# LIMIT 10 ;
>
>
>> -> Subquery Scan vsp (cost=985.73..1016.53
>> rows=1103 width=12) (actual time=25.328..1668.754 rows=493 loops=31)
>> -> Merge Join (cost=985.73..1011.01
>> rows=1103 width=130) (actual time=25.321..1666.666 rows=493 loops=31)
>> Merge Cond: ("outer".id_program =
>> "inner".id_program)
>
>
> The problem to address is in this subquery. That's a total of 31 x
> (1668.754 - 25.328) = 50seconds (about).
>
> Since your query is so simple, I'm guessing v_sc_user_request is a view.
> Can you provide the definition?
Of course:
CREATE OR REPLACE VIEW v_sc_user_request AS
SELECT
*
FROM
v_sat_request vsr LEFT OUTER JOIN v_sc_packages vsp USING ( id_package )
WHERE
vsr.request_time > now() - '1 month'::interval AND
vsr.expired = FALSE
ORDER BY id_sat_request DESC
;
CREATE OR REPLACE VIEW v_sc_packages AS
SELECT
*
FROM
v_programs vpr,
v_packages vpk,
v_sequences vs
WHERE
------------ JOIN -------------
vpr.id_program = vs.id_program AND
vpk.id_package = vs.id_package AND
-------------------------------
vs.estimated_start IS NOT NULL
;
CREATE OR REPLACE VIEW v_sat_request AS
SELECT
*
FROM
sat_request sr,
url u,
user_login ul
WHERE
---------------- JOIN ---------------------
sr.id_url = u.id_url AND
sr.id_user = ul.id_user
-------------------------------------------
;
that column expired was added since yesterday
Regards
Gaetano Mendola
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCLZkD7UpzwH2SGd4RAv8/AKCA5cNfu6vEKZ6m/ke1JsVRdsOTXQCbBMt4
ZPTFjwyb52CrFxdUTD6gejs=
=STzz
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: bad plan
2005-03-08 10:32 bad plan Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 11:39 ` Re: bad plan Richard Huxton <dev@archonet.com>
2005-03-08 12:22 ` Re: bad plan Gaetano Mendola <mendola@bigfoot.com>
@ 2005-03-08 13:20 ` Richard Huxton <dev@archonet.com>
2005-03-08 18:35 ` Re: bad plan Gaetano Mendola <mendola@bigfoot.com>
1 sibling, 1 reply; 19+ messages in thread
From: Richard Huxton @ 2005-03-08 13:20 UTC (permalink / raw)
To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: pgsql-performance
Gaetano Mendola wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> Richard Huxton wrote:
>
>>Gaetano Mendola wrote:
>>
>>
>>>running a 7.4.5 engine, I'm facing this bad plan:
>>>
>>>empdb=# explain analyze SELECT
>>>name,url,descr,request_status,url_status,size_mb,estimated_start,request_time_stamp
>>>
>>>empdb-# FROM v_sc_user_request
>>>empdb-# WHERE
>>>empdb-# login = 'babinow1'
>>>empdb-# LIMIT 10 ;
>>
>>
>>> -> Subquery Scan vsp (cost=985.73..1016.53
>>>rows=1103 width=12) (actual time=25.328..1668.754 rows=493 loops=31)
>>> -> Merge Join (cost=985.73..1011.01
>>>rows=1103 width=130) (actual time=25.321..1666.666 rows=493 loops=31)
>>> Merge Cond: ("outer".id_program =
>>>"inner".id_program)
>>
>>
>>The problem to address is in this subquery. That's a total of 31 x
>>(1668.754 - 25.328) = 50seconds (about).
>>
>>Since your query is so simple, I'm guessing v_sc_user_request is a view.
>>Can you provide the definition?
>
>
> Of course:
>
>
>
> CREATE OR REPLACE VIEW v_sc_user_request AS
> SELECT
> *
> FROM
> v_sat_request vsr LEFT OUTER JOIN v_sc_packages vsp USING ( id_package )
> WHERE
> vsr.request_time > now() - '1 month'::interval AND
> vsr.expired = FALSE
> ORDER BY id_sat_request DESC
> ;
>
>
> CREATE OR REPLACE VIEW v_sc_packages AS
> SELECT
> *
> FROM
> v_programs vpr,
> v_packages vpk,
> v_sequences vs
>
> WHERE
> ------------ JOIN -------------
> vpr.id_program = vs.id_program AND
> vpk.id_package = vs.id_package AND
> -------------------------------
> vs.estimated_start IS NOT NULL
> ;
>
> CREATE OR REPLACE VIEW v_sat_request AS
> SELECT
> *
> FROM
> sat_request sr,
> url u,
> user_login ul
> WHERE
> ---------------- JOIN ---------------------
> sr.id_url = u.id_url AND
> sr.id_user = ul.id_user
> -------------------------------------------
> ;
OK, so looking at the original EXPLAIN the order of processing seems to be:
1. v_sat_request is evaluated and filtered on login='...' (lines 7..15)
This gives us 31 rows
2. The left-join from v_sat_request to v_sc_packages is processed (lines
5..6)
This involves the subquery scan on vsp (from line 16) where it seems to
think the best idea is a merge join of programs to sequences.
So - I think we need to look at the performance of your view
"v_sc_packages" and the views that it depends on. OK - can you reply to
this with just the definitions of v_sc_packages and what it depends on,
and we can have a look at that.
Do you need all these tables involved in this query? I don't think PG is
smart enough to completely discard a join if it's not needed by the
output. Thinking about it, I'm not sure you could safely.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: bad plan
2005-03-08 10:32 bad plan Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 11:39 ` Re: bad plan Richard Huxton <dev@archonet.com>
2005-03-08 12:22 ` Re: bad plan Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 13:20 ` Re: bad plan Richard Huxton <dev@archonet.com>
@ 2005-03-08 18:35 ` Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 19:20 ` Re: bad plan Richard Huxton <dev@archonet.com>
0 siblings, 1 reply; 19+ messages in thread
From: Gaetano Mendola @ 2005-03-08 18:35 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>
Richard Huxton wrote:
> OK, so looking at the original EXPLAIN the order of processing seems to be:
> 1. v_sat_request is evaluated and filtered on login='...' (lines 7..15)
> This gives us 31 rows
> 2. The left-join from v_sat_request to v_sc_packages is processed (lines
> 5..6)
> This involves the subquery scan on vsp (from line 16) where it seems to
> think the best idea is a merge join of programs to sequences.
Whel basically v_sc_packages depends on other 3 views that are just a simple
interface to a plain table.
If I execute a select only on this table I get reasonable executions time:
=== cpu_tuple_cost = 0.07
# explain analyze select * from v_sc_packages where id_package = 19628;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..15.96 rows=1 width=131) (actual time=41.450..41.494 rows=1 loops=1)
-> Nested Loop (cost=0.00..11.86 rows=1 width=116) (actual time=1.022..1.055 rows=1 loops=1)
-> Nested Loop Left Join (cost=0.00..7.89 rows=1 width=104) (actual time=0.330..0.345 rows=1 loops=1)
-> Index Scan using packages_pkey on packages p (cost=0.00..3.90 rows=1 width=104) (actual time=0.070..0.075 rows=1 loops=1)
Index Cond: (id_package = 19628)
-> Index Scan using package_security_id_package_key on package_security ps (cost=0.00..3.91 rows=1 width=4) (actual time=0.232..0.237 rows=1 loops=1)
Index Cond: ("outer".id_package = ps.id_package)
-> Index Scan using idx_sequences_id_package on sequences (cost=0.00..3.90 rows=1 width=16) (actual time=0.670..0.685 rows=1 loops=1)
Index Cond: (19628 = id_package)
Filter: (estimated_start IS NOT NULL)
-> Index Scan using programs_pkey on programs (cost=0.00..4.02 rows=1 width=19) (actual time=0.078..0.086 rows=1 loops=1)
Index Cond: (programs.id_program = "outer".id_program)
Filter: (id_program <> 0)
Total runtime: 42.650 ms
(14 rows)
=== cpu_tuple_cost = 0.01
# explain analyze select * from v_sc_packages where id_package = 19628;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..15.54 rows=1 width=131) (actual time=25.062..69.977 rows=1 loops=1)
-> Nested Loop (cost=0.00..11.56 rows=1 width=116) (actual time=5.396..50.299 rows=1 loops=1)
-> Nested Loop Left Join (cost=0.00..7.71 rows=1 width=104) (actual time=5.223..32.842 rows=1 loops=1)
-> Index Scan using packages_pkey on packages p (cost=0.00..3.84 rows=1 width=104) (actual time=0.815..7.235 rows=1 loops=1)
Index Cond: (id_package = 19628)
-> Index Scan using package_security_id_package_key on package_security ps (cost=0.00..3.85 rows=1 width=4) (actual time=4.366..25.555 rows=1 loops=1)
Index Cond: ("outer".id_package = ps.id_package)
-> Index Scan using idx_sequences_id_package on sequences (cost=0.00..3.84 rows=1 width=16) (actual time=0.147..17.422 rows=1 loops=1)
Index Cond: (19628 = id_package)
Filter: (estimated_start IS NOT NULL)
-> Index Scan using programs_pkey on programs (cost=0.00..3.96 rows=1 width=19) (actual time=0.043..0.049 rows=1 loops=1)
Index Cond: (programs.id_program = "outer".id_program)
Filter: (id_program <> 0)
Total runtime: 70.254 ms
(14 rows)
and I get the best with this:
=== cpu_tuple_cost = 0.001
# explain analyze select * from v_sc_packages where id_package = 19628;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=0.00..15.48 rows=1 width=131) (actual time=2.516..2.553 rows=1 loops=1)
-> Nested Loop (cost=0.00..7.78 rows=1 width=31) (actual time=1.439..1.457 rows=1 loops=1)
-> Index Scan using idx_sequences_id_package on sequences (cost=0.00..3.83 rows=1 width=16) (actual time=0.442..0.450 rows=1 loops=1)
Index Cond: (19628 = id_package)
Filter: (estimated_start IS NOT NULL)
-> Index Scan using programs_pkey on programs (cost=0.00..3.95 rows=1 width=19) (actual time=0.972..0.978 rows=1 loops=1)
Index Cond: (programs.id_program = "outer".id_program)
Filter: (id_program <> 0)
-> Nested Loop Left Join (cost=0.00..7.68 rows=1 width=104) (actual time=0.110..0.125 rows=1 loops=1)
-> Index Scan using packages_pkey on packages p (cost=0.00..3.84 rows=1 width=104) (actual time=0.040..0.046 rows=1 loops=1)
Index Cond: (id_package = 19628)
-> Index Scan using package_security_id_package_key on package_security ps (cost=0.00..3.84 rows=1 width=4) (actual time=0.036..0.042 rows=1 loops=1)
Index Cond: ("outer".id_package = ps.id_package)
Total runtime: 2.878 ms
(14 rows)
but with this last setting for the original query is choosed a very bad plan.
Regards
Gaetano Mendola
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: bad plan
2005-03-08 10:32 bad plan Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 11:39 ` Re: bad plan Richard Huxton <dev@archonet.com>
2005-03-08 12:22 ` Re: bad plan Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 13:20 ` Re: bad plan Richard Huxton <dev@archonet.com>
2005-03-08 18:35 ` Re: bad plan Gaetano Mendola <mendola@bigfoot.com>
@ 2005-03-08 19:20 ` Richard Huxton <dev@archonet.com>
0 siblings, 0 replies; 19+ messages in thread
From: Richard Huxton @ 2005-03-08 19:20 UTC (permalink / raw)
To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: pgsql-performance
Gaetano Mendola wrote:
> Richard Huxton wrote:
>
>
>>OK, so looking at the original EXPLAIN the order of processing seems to be:
>>1. v_sat_request is evaluated and filtered on login='...' (lines 7..15)
>>This gives us 31 rows
>>2. The left-join from v_sat_request to v_sc_packages is processed (lines
>>5..6)
>>This involves the subquery scan on vsp (from line 16) where it seems to
>>think the best idea is a merge join of programs to sequences.
>
>
> Whel basically v_sc_packages depends on other 3 views that are just a simple
> interface to a plain table.
>
>
> If I execute a select only on this table I get reasonable executions time:
>
>
> === cpu_tuple_cost = 0.07
>
> # explain analyze select * from v_sc_packages where id_package = 19628;
> QUERY PLAN
> -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Nested Loop (cost=0.00..15.96 rows=1 width=131) (actual time=41.450..41.494 rows=1 loops=1)
> -> Nested Loop (cost=0.00..11.86 rows=1 width=116) (actual time=1.022..1.055 rows=1 loops=1)
> -> Nested Loop Left Join (cost=0.00..7.89 rows=1 width=104) (actual time=0.330..0.345 rows=1 loops=1)
> -> Index Scan using packages_pkey on packages p (cost=0.00..3.90 rows=1 width=104) (actual time=0.070..0.075 rows=1 loops=1)
> Index Cond: (id_package = 19628)
> -> Index Scan using package_security_id_package_key on package_security ps (cost=0.00..3.91 rows=1 width=4) (actual time=0.232..0.237 rows=1 loops=1)
> Index Cond: ("outer".id_package = ps.id_package)
> -> Index Scan using idx_sequences_id_package on sequences (cost=0.00..3.90 rows=1 width=16) (actual time=0.670..0.685 rows=1 loops=1)
> Index Cond: (19628 = id_package)
> Filter: (estimated_start IS NOT NULL)
> -> Index Scan using programs_pkey on programs (cost=0.00..4.02 rows=1 width=19) (actual time=0.078..0.086 rows=1 loops=1)
> Index Cond: (programs.id_program = "outer".id_program)
> Filter: (id_program <> 0)
> Total runtime: 42.650 ms
> (14 rows)
> === cpu_tuple_cost = 0.01
> === cpu_tuple_cost = 0.001
I don't know what you think you're measuring, but it's nothing to do
with the plans. If you look at the plans carefully, you'll see they're
all the same. The "cost" numbers change because that's the parameter
you're changing.
I'm not sure it makes sense to vary cpu_tuple_cost from 0.07 down to
0.001 - that's a factor of 70 difference. I might be tempted to halve or
double it, but even then only after some serious testing.
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: bad plan
2005-03-08 10:32 bad plan Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 11:39 ` Re: bad plan Richard Huxton <dev@archonet.com>
2005-03-08 12:22 ` Re: bad plan Gaetano Mendola <mendola@bigfoot.com>
@ 2005-03-08 20:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-08 20:25 ` Re: bad plan Richard Huxton <dev@archonet.com>
2005-03-09 00:47 ` Re: bad plan Gaetano Mendola <mendola@bigfoot.com>
1 sibling, 2 replies; 19+ messages in thread
From: Tom Lane @ 2005-03-08 20:01 UTC (permalink / raw)
To: Gaetano Mendola <mendola@bigfoot.com>; +Cc: Richard Huxton <dev@archonet.com>; pgsql-performance
Gaetano Mendola <mendola@bigfoot.com> writes:
>> Since your query is so simple, I'm guessing v_sc_user_request is a view.
>> Can you provide the definition?
> Of course:
I don't think you've told us the whole truth about the v_sc_packages
view. The definition as given doesn't work at all (it'll have
duplicate column names), but more to the point, if it were that simple
then the planner would fold it into the parent query. The subquery
scan node indicates that folding did not occur. The most likely reason
for that is that there's an ORDER BY in the view.
Putting ORDER BYs in views that you intend to use as components of other
views is a bad practice from a performance perspective...
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: bad plan
2005-03-08 10:32 bad plan Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 11:39 ` Re: bad plan Richard Huxton <dev@archonet.com>
2005-03-08 12:22 ` Re: bad plan Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 20:01 ` Re: bad plan Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-03-08 20:25 ` Richard Huxton <dev@archonet.com>
2005-03-08 20:42 ` Re: bad plan Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 19+ messages in thread
From: Richard Huxton @ 2005-03-08 20:25 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Gaetano Mendola <mendola@bigfoot.com>; pgsql-performance
Tom Lane wrote:
>
> Putting ORDER BYs in views that you intend to use as components of other
> views is a bad practice from a performance perspective...
There are also a lot of views involved here for very few output columns.
Tom - is the planner smart enough to optimise-out unneeded columns from
a SELECT * view if it's part of a join/subquery and you only use one or
two columns?
Secondly, in the original plan we have:
-> Nested Loop Left Join (cost=1478.82..1716.37 rows=1 width=201)
(actual time=3254.483..52847.064 rows=31 loops=1)
Now, we've got 31 rows instead of 1 here. The one side of the join ends
up as:
-> Subquery Scan vsp (cost=985.73..1016.53 rows=1103 width=12) (actual
time=25.328..1668.754 rows=493 loops=31)
-> Merge Join (cost=985.73..1011.01 rows=1103 width=130) (actual
time=25.321..1666.666 rows=493 loops=31)
Would I be right in thinking the planner doesn't materialise the
subquery because it's expecting 1 loop not 31? If there were 1 row the
plan would seem OK to me.
Is there any mileage in the idea of a "lazy" planner that keeps some
alternative paths around in case they're needed? Or a reactive one that
can re-plan nodes when assumptions turn out to be wrong?
--
Richard Huxton
Archonet Ltd
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: bad plan
2005-03-08 10:32 bad plan Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 11:39 ` Re: bad plan Richard Huxton <dev@archonet.com>
2005-03-08 12:22 ` Re: bad plan Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 20:01 ` Re: bad plan Tom Lane <tgl@sss.pgh.pa.us>
2005-03-08 20:25 ` Re: bad plan Richard Huxton <dev@archonet.com>
@ 2005-03-08 20:42 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 19+ messages in thread
From: Tom Lane @ 2005-03-08 20:42 UTC (permalink / raw)
To: Richard Huxton <dev@archonet.com>; +Cc: Gaetano Mendola <mendola@bigfoot.com>; pgsql-performance
Richard Huxton <dev@archonet.com> writes:
> There are also a lot of views involved here for very few output columns.
> Tom - is the planner smart enough to optimise-out unneeded columns from
> a SELECT * view if it's part of a join/subquery and you only use one or
> two columns?
If the view gets flattened, yes, but I believe that it's not bright
enough to do so when it can't flatten the view. You could tell easily
enough by looking at the row-width estimates at various levels of the
plan. (Let's see ... in Gaetano's plan the SubqueryScan is returning
12-byte rows where its input MergeJoin is returning 130-byte rows,
so sure enough the view is computing a lot of stuff that then gets
thrown away.)
> Would I be right in thinking the planner doesn't materialise the
> subquery because it's expecting 1 loop not 31? If there were 1 row the
> plan would seem OK to me.
Right; it doesn't see any predicted gain from the extra cost of
materializing. But to me the main problem here is not that, it is that
the entire shape of the plan would likely be different if it weren't for
the "optimization fence" that the Subquery Scan node represents. I
suspect too that the use of mergejoin as opposed to anything else within
the vsp subplan is driven more by the need to produce sorted output than
by what is the cheapest way to get the rows.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: bad plan
2005-03-08 10:32 bad plan Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 11:39 ` Re: bad plan Richard Huxton <dev@archonet.com>
2005-03-08 12:22 ` Re: bad plan Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 20:01 ` Re: bad plan Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-03-09 00:47 ` Gaetano Mendola <mendola@bigfoot.com>
1 sibling, 0 replies; 19+ messages in thread
From: Gaetano Mendola @ 2005-03-09 00:47 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Tom Lane wrote:
> Gaetano Mendola <mendola@bigfoot.com> writes:
>
>>>Since your query is so simple, I'm guessing v_sc_user_request is a view.
>>>Can you provide the definition?
>
>
>>Of course:
>
>
> I don't think you've told us the whole truth about the v_sc_packages
> view. The definition as given doesn't work at all (it'll have
> duplicate column names), but more to the point, if it were that simple
> then the planner would fold it into the parent query. The subquery
> scan node indicates that folding did not occur. The most likely reason
> for that is that there's an ORDER BY in the view.
I didn't say the complete truth because the view definition is long so I just omitted
all fields.
explain analyze SELECT name,url,descr,request_status,url_status,size_mb,estimated_start,request_time_stamp
FROM v_sc_user_request
WHERE login = 'babinow1'
LIMIT 10 ;
these are the complete definitions of views involved in the query:
CREATE OR REPLACE VIEW v_sc_user_request AS
SELECT
vsr.id_sat_request AS id_sat_request,
vsr.id_user AS id_user,
vsr.login AS login,
vsr.url AS url,
vsr.name AS name,
vsr.descr AS descr,
vsr.size AS size,
trunc(vsr.size/1024.0/1024.0,2) AS size_mb,
vsr.id_sat_request_status AS id_sat_request_status,
sp_lookup_key('sat_request_status', vsr.id_sat_request_status) AS request_status,
sp_lookup_descr('sat_request_status', vsr.id_sat_request_status) AS request_status_descr,
vsr.id_url_status AS id_url_status,
sp_lookup_key('url_status', vsr.id_url_status) AS url_status,
sp_lookup_descr('url_status', vsr.id_url_status) AS url_status_descr,
vsr.url_time_stamp AS url_time_stamp,
date_trunc('seconds',vsr.request_time) AS request_time_stamp,
vsr.id_package AS id_package,
COALESCE(date_trunc('seconds',vsp.estimated_start)::text,'NA') AS estimated_start
FROM
v_sat_request vsr LEFT OUTER JOIN v_sc_packages vsp USING ( id_package )
WHERE
vsr.request_time > now() - '1 month'::interval AND
vsr.expired = FALSE
ORDER BY id_sat_request DESC
;
CREATE OR REPLACE VIEW v_sat_request AS
SELECT
sr.id_user AS id_user,
ul.login AS login,
sr.id_sat_request AS id_sat_request,
u.id_url AS id_url,
u.url AS url,
u.name AS name,
u.descr AS descr,
u.size AS size,
u.storage AS storage,
sr.id_package AS id_package,
sr.id_sat_request_status AS id_sat_request_status,
sr.request_time AS request_time,
sr.work_time AS request_work_time,
u.id_url_status AS id_url_status,
u.time_stamp AS url_time_stamp,
sr.expired AS expired
FROM
sat_request sr,
url u,
user_login ul
WHERE
---------------- JOIN ---------------------
sr.id_url = u.id_url AND
sr.id_user = ul.id_user
-------------------------------------------
;
CREATE OR REPLACE VIEW v_sc_packages AS
SELECT
vpr.id_program AS id_program,
vpr.name AS program_name,
vpk.id_package AS id_package,
date_trunc('seconds', vs.estimated_start) AS estimated_start,
vpk.name AS package_name,
vpk.TYPE AS TYPE,
vpk.description AS description,
vpk.target AS target,
vpk.fec AS fec_alg,
vpk.output_group - vpk.input_group AS fec_redundancy,
vpk.priority AS priority,
vpk.updatable AS updatable,
vpk.auto_listen AS auto_listen,
vpk.start_file AS start_file,
vpk.view_target_group AS view_target_group,
vpk.target_group AS target_group
FROM
v_programs vpr,
v_packages vpk,
v_sequences vs
WHERE
------------ JOIN -------------
vpr.id_program = vs.id_program AND
vpk.id_package = vs.id_package AND
-------------------------------
vs.estimated_start IS NOT NULL
;
CREATE OR REPLACE VIEW v_programs AS
SELECT id_program AS id_program,
id_publisher AS id_publisher,
name AS name,
description AS description,
sp_lookup_key('program_type', id_program_type) AS TYPE,
sp_lookup_key('program_status', id_program_status) AS status,
last_position AS last_position
FROM programs
WHERE id_program<>0
;
CREATE OR REPLACE VIEW v_packages AS
SELECT p.id_package AS id_package,
p.id_publisher AS id_publisher,
p.name AS name,
p.information AS information,
p.description AS description,
sp_lookup_key('package_type', p.id_package_type)
AS TYPE,
sp_lookup_key('target', p.id_target)
AS target,
p.port AS port,
p.priority AS priority,
sp_lookup_key('fec', p.id_fec)
AS fec,
p.input_group AS input_group,
p.output_group AS output_group,
p.updatable AS updatable,
p.checksum AS checksum,
p.version AS version,
p.start_file AS start_file,
p.view_target_group AS view_target_group,
p.target_group AS target_group,
p.auto_listen AS auto_listen,
p.public_flag AS public_flag,
p.needed_version AS needed_version,
p.logic_version AS logic_version,
p.package_size AS package_size,
ps.id_drm_process AS id_drm_process,
ps.id_cas_service AS id_cas_service,
ps.id_cas_settings AS id_cas_settings,
ps.id_drm_service AS id_drm_service
FROM packages p LEFT OUTER JOIN package_security ps USING (id_package)
;
CREATE OR REPLACE VIEW v_sequences AS
SELECT id_package AS id_package,
id_program AS id_program,
internal_position AS internal_position,
estimated_start AS estimated_start
FROM sequences
;
> Putting ORDER BYs in views that you intend to use as components of other
> views is a bad practice from a performance perspective...
Indeed when a view is involved in a join we do not put "order by" in it ( at
least this is what I try to do ), I have to say also that some time I see that replacing
the view with the tables that it represent the execution time is better
( I have an example to show you if you are interested in it ).
Regards
Gaetano Mendola
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.2.5 (MingW32)
Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
iD8DBQFCLkef7UpzwH2SGd4RAt90AJ9e3qUSx2fxiOO2aA30TbLsOdyV7ACfd0RY
+2A3U6dDfWw/H4eWcmI8mS0=
=t1AD
-----END PGP SIGNATURE-----
^ permalink raw reply [nested|flat] 19+ messages in thread
* bad plan
@ 2012-04-05 11:47 Julien Cigar <jcigar@ulb.ac.be>
2012-04-05 14:26 ` Re: bad plan Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-04-05 19:47 ` Re: bad plan Ants Aasma <ants@cybertec.at>
0 siblings, 2 replies; 19+ messages in thread
From: Julien Cigar @ 2012-04-05 11:47 UTC (permalink / raw)
To: pgsql-performance
Hello,
I have an extremely bad plan for one of my colleague's query. Basically
PostgreSQL chooses to seq scan instead of index scan. This is on:
antabif=# select version();
version
----------------------------------------------------------------------------------------------------------
PostgreSQL 9.0.7 on amd64-portbld-freebsd8.2, compiled by GCC cc (GCC)
4.2.1 20070719 [FreeBSD], 64-bit
The machines has 4GB of RAM with the following config:
- shared_buffers: 512MB
- effective_cache_size: 2GB
- work_mem: 32MB
- maintenance_work_mem: 128MB
- default_statistics_target: 300
- temp_buffers: 64MB
- wal_buffers: 8MB
- checkpoint_segments = 15
The tables have been ANALYZE'd. I've put the EXPLAIN ANALYZE on:
- http://www.pastie.org/3731956 : with default config
- http://www.pastie.org/3731960 : this is with enable_seq_scan = off
- http://www.pastie.org/3731962 : I tried to play on the various cost
settings but it's doesn't change anything, except setting
random_page_cost to 1 (which will lead to bad plans for other queries,
so not a solution)
- http://www.pastie.org/3732035 : with enable_hashagg and
enable_hashjoin to false
I'm currently out of idea why PostgreSQL still chooses a bad plan for
this query ... any hint ?
Thank you,
Julien
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
Attachments:
[text/x-vcard] jcigar.vcf (290B, ../../4F7D8655.8080407@ulb.ac.be/2-jcigar.vcf)
download | inline:
begin:vcard
fn:Julien Cigar
n:Cigar;Julien
org;quoted-printable:Belgian Biodiversity Platform;ULB (Universit=C3=A9 Libre de Bruxelles)
adr:;;;Brussels;;;Belgium
email;internet:jcigar@ulb.ac.be
tel;work:+32(0)26505752
x-mozilla-html:FALSE
url:http://www.biodiversity.be
version:2.1
end:vcard
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: bad plan
2012-04-05 11:47 bad plan Julien Cigar <jcigar@ulb.ac.be>
@ 2012-04-05 14:26 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 19+ messages in thread
From: Kevin Grittner @ 2012-04-05 14:26 UTC (permalink / raw)
To: pgsql-performance; Julien Cigar <jcigar@ulb.ac.be>
Julien Cigar <jcigar@ulb.ac.be> wrote:
> I tried to play on the various cost settings but it's doesn't
> change anything, except setting random_page_cost to 1 (which will
> lead to bad plans for other queries, so not a solution)
Yeah, you clearly don't have the active portion of your database
fully cached, so you don't want random_page_cost to go as low as
seq_page_cost.
Here's one suggestion to try:
random_page_cost = 2
cpu_tuple_cost = 0.05
I have found that combination to work well for me when the level of
caching is about where you're seeing it. I am becoming increasingly
of the opinion that the default for cpu_tuple_cost should be higher
than 0.01.
Please let us know whether that helps.
-Kevin
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: bad plan
2012-04-05 11:47 bad plan Julien Cigar <jcigar@ulb.ac.be>
@ 2012-04-05 19:47 ` Ants Aasma <ants@cybertec.at>
2012-04-06 11:16 ` Re: bad plan Julien Cigar <jcigar@ulb.ac.be>
1 sibling, 1 reply; 19+ messages in thread
From: Ants Aasma @ 2012-04-05 19:47 UTC (permalink / raw)
To: Julien Cigar <jcigar@ulb.ac.be>; +Cc: pgsql-performance
On Thu, Apr 5, 2012 at 2:47 PM, Julien Cigar <jcigar@ulb.ac.be> wrote:
> - http://www.pastie.org/3731956 : with default config
> - http://www.pastie.org/3731960 : this is with enable_seq_scan = off
It looks like the join selectivity of (context_to_context_links,
ancestors) is being overestimated by almost two orders of magnitude.
The optimizer thinks that there are 564 rows in the
context_to_context_links table for each taxon_id, while in fact for
this query the number is 9. To confirm that this, you can force the
selectivity estimate to be 200x lower by adding a geo_id = geod_id
where clause to the subquery.
If it does help, then the next question would be why is the estimate
so much off. It could be either because the stats for
context_to_context_links.taxon_id are wrong or because
ancestors.taxon_id(subphylum_id = 18830) is a special case. To help
figuring this is out, you could run the following to queries and post
the results:
SELECT floor(log(num,2)) AS nmatch, COUNT(*) AS freq FROM (SELECT
COUNT(*) AS num FROM context_to_context_links GROUP BY taxon_id) AS
dist GROUP BY 1 ORDER BY 1;
SELECT floor(log(num,2)) AS nmatch, COUNT(*) AS freq FROM (SELECT
COUNT(*) AS num FROM context_to_context_links WHERE NOT geo_id IS NULL
and taxon_id= ANY ( select taxon_id from rab.ancestors where
ancestors.subphylum_id = 18830) GROUP BY taxon_id) AS dist GROUP BY
1 ORDER BY 1;
If the second distribution has a significantly different shape then
cross column statistics are necessary to get good plans. As it happens
I'm working on adding this functionality to PostgreSQL and would love
to hear more details about your use-case to understand if it would be
solved by this work.
Regards,
Ants Aasma
--
Cybertec Schönig & Schönig GmbH
Gröhrmühlgasse 26
A-2700 Wiener Neustadt
Web: http://www.postgresql-support.de
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: bad plan
2012-04-05 11:47 bad plan Julien Cigar <jcigar@ulb.ac.be>
2012-04-05 19:47 ` Re: bad plan Ants Aasma <ants@cybertec.at>
@ 2012-04-06 11:16 ` Julien Cigar <jcigar@ulb.ac.be>
0 siblings, 0 replies; 19+ messages in thread
From: Julien Cigar @ 2012-04-06 11:16 UTC (permalink / raw)
To: Ants Aasma <ants@cybertec.at>; +Cc: pgsql-performance
On 04/05/2012 21:47, Ants Aasma wrote:
> On Thu, Apr 5, 2012 at 2:47 PM, Julien Cigar<jcigar@ulb.ac.be> wrote:
>> - http://www.pastie.org/3731956 : with default config
>> - http://www.pastie.org/3731960 : this is with enable_seq_scan = off
> It looks like the join selectivity of (context_to_context_links,
> ancestors) is being overestimated by almost two orders of magnitude.
> The optimizer thinks that there are 564 rows in the
> context_to_context_links table for each taxon_id, while in fact for
> this query the number is 9. To confirm that this, you can force the
> selectivity estimate to be 200x lower by adding a geo_id = geod_id
> where clause to the subquery.
adding a geo_id = geo_id to the subquery helped a little bit with a
cpu_tuple_cost of 0.1: http://www.pastie.org/3738224 :
without:
Index Scan using ltlc_taxon_id_idxoncontext_to_context_links (cost=0.00..146.93 rows=341 width=8) (actual time=0.004..0.019 rows=9 loops=736)
with geo_id = geo_id:
Index Scan using ltlc_taxon_id_idxoncontext_to_context_links (cost=0.00..148.11 rows=2 width=8) (actual time=0.004..0.020 rows=9 loops=736)
> If it does help, then the next question would be why is the estimate
> so much off. It could be either because the stats for
> context_to_context_links.taxon_id are wrong or because
> ancestors.taxon_id(subphylum_id = 18830) is a special case. To help
> figuring this is out, you could run the following to queries and post
> the results:
>
> SELECT floor(log(num,2)) AS nmatch, COUNT(*) AS freq FROM (SELECT
> COUNT(*) AS num FROM context_to_context_links GROUP BY taxon_id) AS
> dist GROUP BY 1 ORDER BY 1;
>
> SELECT floor(log(num,2)) AS nmatch, COUNT(*) AS freq FROM (SELECT
> COUNT(*) AS num FROM context_to_context_links WHERE NOT geo_id IS NULL
> and taxon_id= ANY ( select taxon_id from rab.ancestors where
> ancestors.subphylum_id = 18830) GROUP BY taxon_id) AS dist GROUP BY
> 1 ORDER BY 1;
I'm sorry but I get an "ERROR: division by zero" for both of your queries..
> If the second distribution has a significantly different shape then
> cross column statistics are necessary to get good plans. As it happens
> I'm working on adding this functionality to PostgreSQL and would love
> to hear more details about your use-case to understand if it would be
> solved by this work.
Thank you for your help,
Julien
> Regards,
> Ants Aasma
--
No trees were killed in the creation of this message.
However, many electrons were terribly inconvenienced.
Attachments:
[text/x-vcard] jcigar.vcf (290B, ../../4F7ED097.70807@ulb.ac.be/2-jcigar.vcf)
download | inline:
begin:vcard
fn:Julien Cigar
n:Cigar;Julien
org;quoted-printable:Belgian Biodiversity Platform;ULB (Universit=C3=A9 Libre de Bruxelles)
adr:;;;Brussels;;;Belgium
email;internet:jcigar@ulb.ac.be
tel;work:+32(0)26505752
x-mozilla-html:FALSE
url:http://www.biodiversity.be
version:2.1
end:vcard
^ permalink raw reply [nested|flat] 19+ messages in thread
* Bad plan
@ 2018-01-23 12:03 Laurent Martelli <martellilaurent@gmail.com>
2018-01-23 15:18 ` Re: Bad plan Justin Pryzby <pryzby@telsasoft.com>
0 siblings, 1 reply; 19+ messages in thread
From: Laurent Martelli @ 2018-01-23 12:03 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
Hello all,
So I have a view, for which I can select all rows in about 3s (returns ~80k
rows), but if I add a where clause on a column, it takes +300s to return
the ~8k lines.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bad plan
2018-01-23 12:03 Bad plan Laurent Martelli <martellilaurent@gmail.com>
@ 2018-01-23 15:18 ` Justin Pryzby <pryzby@telsasoft.com>
2018-01-23 15:38 ` Re: Bad plan Laurent Martelli <martellilaurent@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Justin Pryzby @ 2018-01-23 15:18 UTC (permalink / raw)
To: Laurent Martelli <martellilaurent@gmail.com>; +Cc: pgsql-performance@lists.postgresql.org
On Tue, Jan 23, 2018 at 01:03:49PM +0100, Laurent Martelli wrote:
> Hello all,
>
> So I have a view, for which I can select all rows in about 3s (returns ~80k
> rows), but if I add a where clause on a column, it takes +300s to return
> the ~8k lines.
>
> From the plan, I see that it expects to return only 1 row and so choose to
> perform some nested loops. Of course, I did run "ANALYZE", but with no
> success.
>
> I managed to speed things up with "set enable_nestloop = false;", but is
> that the only choice I have ? Should I report a bug ?
> Here is the default plan :
Can you resend without line breaks or paste a link to explain.depesz?
The problem appears to be here:
-> Nested Loop Left Join (cost=32067.09..39197.85 rows=1 width=276) (actual time=342.725..340775.031 rows=7359 loops=1)
Join Filter: (sh.share_holder_partner = partner.id)
Rows Removed by Join Filter: 204915707
Justin
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bad plan
2018-01-23 12:03 Bad plan Laurent Martelli <martellilaurent@gmail.com>
2018-01-23 15:18 ` Re: Bad plan Justin Pryzby <pryzby@telsasoft.com>
@ 2018-01-23 15:38 ` Laurent Martelli <martellilaurent@gmail.com>
2018-01-23 15:59 ` Re: Bad plan Laurent Martelli <martellilaurent@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Laurent Martelli @ 2018-01-23 15:38 UTC (permalink / raw)
To: Justin Pryzby <pryzby@telsasoft.com>; +Cc: pgsql-performance@lists.postgresql.org
2018-01-23 16:18 GMT+01:00 Justin Pryzby <pryzby@telsasoft.com>:
> On Tue, Jan 23, 2018 at 01:03:49PM +0100, Laurent Martelli wrote:
>
>> Here is the default plan :
>
> Can you resend without line breaks or paste a link to explain.depesz?
I hope it's better like that. I've attached it too, just in case.
>
> The problem appears to be here:
>
> -> Nested Loop Left Join (cost=32067.09..39197.85 rows=1 width=276) (actual time=342.725..340775.031 rows=7359 loops=1)
> Join Filter: (sh.share_holder_partner = partner.id)
> Rows Removed by Join Filter: 204915707
>
> Justin
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=39200.76..39200.76 rows=1 width=1066) (actual
time=341273.300..341274.244 rows=7359 loops=1)
Sort Key: ((array_agg(subscribed_power.subscribed_power))[1]) DESC,
status.name, contractline.id
Sort Method: quicksort Memory: 3930kB
-> Nested Loop Left Join (cost=32069.19..39200.75 rows=1
width=1066) (actual time=342.806..341203.151 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32069.05..39200.50 rows=1
width=508) (actual time=342.784..341102.848 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32068.77..39200.20
rows=1 width=500) (actual time=342.778..341070.310 rows=7359 loops=1)
-> Nested Loop Left Join
(cost=32068.64..39200.04 rows=1 width=507) (actual
time=342.776..341058.256 rows=7359 loops=1)
Join Filter: (cca.address = adr_contact.id)
Rows Removed by Join Filter: 2254
-> Nested Loop Left Join
(cost=32068.22..39199.55 rows=1 width=515) (actual
time=342.767..340997.058 rows=7359 loops=1)
-> Nested Loop Left Join
(cost=32067.79..39198.84 rows=1 width=447) (actual
time=342.753..340932.286 rows=7359 loops=1)
-> Nested Loop Left Join
(cost=32067.65..39198.67 rows=1 width=421) (actual
time=342.748..340896.132 rows=7359 loops=1)
-> Nested Loop Left Join
(cost=32067.23..39198.01 rows=1 width=279) (actual
time=342.739..340821.987 rows=7359 loops=1)
-> Nested Loop
Left Join (cost=32067.09..39197.85 rows=1 width=276) (actual
time=342.725..340775.031 rows=7359 loops=1)
Join Filter:
(sh.share_holder_partner = partner.id)
Rows Removed
by Join Filter: 204915707
-> Nested
Loop Left Join (cost=28514.61..34092.46 rows=1 width=244) (actual
time=287.323..610.192 rows=7359 loops=1)
->
Nested Loop Left Join (cost=28514.47..34092.30 rows=1 width=239)
(actual time=287.318..573.234 rows=7359 loops=1)
-> Hash Right Join (cost=28513.48..34090.65 rows=1 width=159)
(actual time=287.293..379.564 rows=7359 loops=1)
Hash Cond: (ws.contract_line = contractline.id)
-> Seq Scan on shareholder_web_subscription ws
(cost=0.00..5378.84 rows=52884 width=24) (actual time=0.006..12.307
rows=52884 loops=1)
-> Hash (cost=28513.47..28513.47 rows=1 width=143) (actual
time=287.243..287.243 rows=7359 loops=1)
Buckets: 8192 (originally 1024) Batches: 1 (originally 1)
Memory Usage: 1173kB
-> Nested Loop Left Join (cost=17456.16..28513.47 rows=1
width=143) (actual time=85.005..284.689 rows=7359 loops=1)
-> Nested Loop (cost=17456.03..28513.31 rows=1
width=148) (actual time=85.000..276.599 rows=7359 loops=1)
-> Nested Loop Left Join
(cost=17455.73..28512.84 rows=1 width=148) (actual
time=84.993..261.954 rows=7359 loops=1)
-> Nested Loop (cost=17455.60..28512.67
rows=1 width=140) (actual time=84.989..253.715 rows=7359 loops=1)
-> Nested Loop
(cost=17455.18..28511.93 rows=1 width=93) (actual time=84.981..230.977
rows=7359 loops=1)
-> Merge Right Join
(cost=17454.89..28511.52 rows=1 width=93) (actual time=84.974..211.200
rows=7359 loops=1)
Merge Cond:
(subscribed_power.amendment = amendment.id)
-> GroupAggregate
(cost=12457.78..22574.03 rows=75229 width=168) (actual
time=57.500..175.674 rows=83432 loops=1)
Group Key:
subscribed_power.amendment
-> Merge Join
(cost=12457.78..20764.08 rows=173917 width=12) (actual
time=57.479..129.530 rows=87938 loops=1)
Merge Cond:
(subscribed_power.amendment = amendment_1.id)
-> Index
Scan using contract_subscribed_power_amendment_idx on
contract_subscribed_power subscribed_power (cost=0.42..13523.09
rows=173917 width=12) (actual time=0.009..33.704 rows=87963 loops=1)
-> Sort
(cost=12457.36..12666.43 rows=83629 width=8) (actual
time=57.467..67.071 rows=88019 loops=1)
Sort
Key: amendment_1.id
Sort
Method: quicksort Memory: 6988kB
->
Hash Join (cost=10.21..5619.97 rows=83629 width=8) (actual
time=0.112..40.965 rows=83532 loops=1)
Hash Cond: (amendment_1.pricing = pricing.id)
-> Seq Scan on contract_amendment amendment_1 (cost=0.00..4460.29
rows=83629 width=16) (actual time=0.004..6.988 rows=83629 loops=1)
-> Hash (cost=8.43..8.43 rows=142 width=8) (actual time=0.095..0.095
rows=141 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 14kB
-> Hash Join (cost=1.07..8.43 rows=142 width=8) (actual
time=0.012..0.078 rows=141 loops=1)
Hash Cond: (pricing.elec_range = elec_range.id)
-> Seq Scan on pricing_pricing pricing (cost=0.00..5.42
rows=142 width=16) (actual time=0.003..0.015 rows=142 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=8) (actual
time=0.006..0.006 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fluid_elec_range elec_range
(cost=0.00..1.03 rows=3 width=8) (actual time=0.003..0.005 rows=3
loops=1)
-> Sort
(cost=4997.11..4997.11 rows=1 width=69) (actual time=27.427..28.896
rows=7359 loops=1)
Sort Key:
amendment.id
Sort Method:
quicksort Memory: 1227kB
-> Nested Loop
(cost=183.44..4997.10 rows=1 width=69) (actual time=1.115..24.616
rows=7359 loops=1)
-> Nested
Loop (cost=183.15..4996.59 rows=1 width=49) (actual time=1.107..9.091
rows=7360 loops=1)
->
Index Scan using uk_3b1y5vw9gmh7u3jj8aa2uy0b9 on contact_partner
businessprovider (cost=0.42..8.44 rows=1 width=13) (actual
time=0.010..0.010 rows=1 loops=1)
Index Cond: ((business_provider_code)::text = 'BRZH'::text)
->
Bitmap Heap Scan on contract_contract_line contractline
(cost=182.73..4907.58 rows=8057 width=52) (actual time=1.086..5.231
rows=7360 loops=1)
Recheck Cond: (business_provider_partner = businessprovider.id)
Heap Blocks: exact=3586
-> Bitmap Index Scan on
contract_contract_line_business_provider_partner_idx
(cost=0.00..180.72 rows=8057 width=0) (actual time=0.655..0.655
rows=7360 loops=1)
Index Cond: (business_provider_partner = businessprovider.id)
-> Index
Scan using contract_amendment_pkey on contract_amendment amendment
(cost=0.29..0.50 rows=1 width=28) (actual time=0.001..0.002 rows=1
loops=7360)
Index
Cond: (id = contractline.amendment)
-> Index Scan using
contract_contract_pkey on contract_contract contract (cost=0.29..0.40
rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=7359)
Index Cond: (id =
contractline.contract)
-> Index Scan using
contact_partner_pkey on contact_partner partner (cost=0.42..0.74
rows=1 width=55) (actual time=0.002..0.002 rows=1 loops=7359)
Index Cond: (id =
contract.main_client_partner)
-> Index Scan using
contact_client_nature_pkey on contact_client_nature clientnature
(cost=0.14..0.15 rows=1 width=24) (actual time=0.001..0.001 rows=1
loops=7359)
Index Cond: (id =
partner.client_nature)
-> Index Scan using territory_mpt_pkey on
territory_mpt mpt (cost=0.29..0.46 rows=1 width=16) (actual
time=0.001..0.001 rows=1 loops=7359)
Index Cond: (id = contractline.mpt)
-> Index Scan using contract_user_segment_pkey on
contract_user_segment usersegment (cost=0.14..0.15 rows=1 width=11)
(actual time=0.001..0.001 rows=1 loops=7359)
Index Cond: (id = amendment.user_segment)
-> Nested Loop Left Join (cost=0.99..1.64 rows=1 width=96) (actual
time=0.021..0.025 rows=1 loops=7359)
-> Nested Loop Left Join (cost=0.85..1.35 rows=1 width=89)
(actual time=0.017..0.020 rows=1 loops=7359)
-> Nested Loop Left Join (cost=0.71..1.18 rows=1 width=76)
(actual time=0.013..0.014 rows=1 loops=7359)
-> Index Scan using contact_address_pkey on
contact_address a (cost=0.42..0.85 rows=1 width=84) (actual
time=0.005..0.006 rows=1 loops=7359)
Index Cond: (mpt.address = id)
-> Index Scan using territory_commune_pkey on
territory_commune commune (cost=0.29..0.32 rows=1 width=16) (actual
time=0.005..0.006 rows=1 loops=7359)
Index Cond: (a.commune = id)
-> Index Scan using territory_department_pkey on
territory_department dept (cost=0.14..0.16 rows=1 width=37) (actual
time=0.003..0.004 rows=1 loops=7359)
Index Cond: (commune.department = id)
-> Index Scan using territory_region_pkey on territory_region reg
(cost=0.14..0.27 rows=1 width=23) (actual time=0.003..0.003 rows=1
loops=7359)
Index Cond: (dept.region = id)
->
Index Scan using administration_status_pkey on administration_status
status (cost=0.14..0.16 rows=1 width=21) (actual time=0.003..0.003
rows=1 loops=7359)
Index Cond: (id = contractline.status)
->
GroupAggregate (cost=3552.48..4479.27 rows=27827 width=80) (actual
time=0.006..44.205 rows=27846 loops=7359)
Group
Key: sh.share_holder_partner
->
Sort (cost=3552.48..3624.85 rows=28948 width=17) (actual
time=0.003..2.913 rows=28946 loops=7359)
Sort Key: sh.share_holder_partner
Sort Method: quicksort Memory: 3030kB
-> Hash Join (cost=2.23..1407.26 rows=28948 width=17) (actual
time=0.024..12.296 rows=28946 loops=1)
Hash Cond: (sh.company = sh_coop.id)
-> Seq Scan on shareholder_share_holder sh (cost=0.00..1007.00
rows=28948 width=20) (actual time=0.007..5.495 rows=28946 loops=1)
Filter: (nb_share > 0)
Rows Removed by Filter: 1934
-> Hash (cost=2.10..2.10 rows=10 width=13) (actual
time=0.009..0.009 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on contact_company sh_coop (cost=0.00..2.10
rows=10 width=13) (actual time=0.003..0.006 rows=10 loops=1)
-> Index Scan
using crm_origin_pkey on crm_origin co (cost=0.14..0.16 rows=1
width=19) (actual time=0.004..0.004 rows=1 loops=7359)
Index Cond:
(id = ws.how_meet_enercoop)
-> Index Scan using
contact_contact_pkey on contact_contact mc (cost=0.42..0.65 rows=1
width=150) (actual time=0.007..0.008 rows=1 loops=7359)
Index Cond:
(partner.main_contact = id)
-> Index Scan using
contact_title_pkey on contact_title title (cost=0.14..0.16 rows=1
width=42) (actual time=0.003..0.003 rows=1 loops=7359)
Index Cond: (mc.title = id)
-> Index Scan using
contact_address_pkey on contact_address adr_contact (cost=0.43..0.70
rows=1 width=68) (actual time=0.005..0.005 rows=1 loops=7359)
Index Cond: (id = CASE WHEN
(CASE WHEN ((partner.person_category_select)::text =
'naturalPerson'::text) THEN 'P'::text WHEN
((partner.person_category_select)::text = 'legalPerson'::text) THEN
'M'::text ELSE '?????'::text END = 'P'::text) THEN
COALESCE(mc.address, mc.address_pro) ELSE COALESCE(mc.address_pro,
mc.address) END)
-> Index Scan using
contact_contact_address_contact_idx on contact_contact_address cca
(cost=0.42..0.48 rows=1 width=24) (actual time=0.006..0.006 rows=1
loops=7359)
Index Cond: (contact = mc.id)
-> Index Scan using
contact_contact_address_status_pkey on contact_contact_address_status
npai (cost=0.13..0.15 rows=1 width=9) (actual time=0.000..0.000
rows=0 loops=7359)
Index Cond: (cca.contact_address_status = id)
-> Index Scan using
crm_crm_request_original_contract_line_idx on crm_crm_request
mesrequest (cost=0.28..0.29 rows=1 width=16) (actual
time=0.003..0.003 rows=0 loops=7359)
Index Cond: (original_contract_line = contractline.id)
-> Index Scan using sale_product_sub_family_pkey on
sale_product_sub_family mesproductsubfamily (cost=0.14..0.20 rows=1
width=62) (actual time=0.000..0.000 rows=0 loops=7359)
Index Cond: (id = mesrequest.product_sub_family)
Filter: (new_contract_ok IS TRUE)
Planning time: 21.106 ms
Execution time: 341275.027 ms
(118 lignes)
--
http://www.laurentmartelli.com // http://www.imprimart.fr
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=39200.76..39200.76 rows=1 width=1066) (actual time=341273.300..341274.244 rows=7359 loops=1)
Sort Key: ((array_agg(subscribed_power.subscribed_power))[1]) DESC, status.name, contractline.id
Sort Method: quicksort Memory: 3930kB
-> Nested Loop Left Join (cost=32069.19..39200.75 rows=1 width=1066) (actual time=342.806..341203.151 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32069.05..39200.50 rows=1 width=508) (actual time=342.784..341102.848 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32068.77..39200.20 rows=1 width=500) (actual time=342.778..341070.310 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32068.64..39200.04 rows=1 width=507) (actual time=342.776..341058.256 rows=7359 loops=1)
Join Filter: (cca.address = adr_contact.id)
Rows Removed by Join Filter: 2254
-> Nested Loop Left Join (cost=32068.22..39199.55 rows=1 width=515) (actual time=342.767..340997.058 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32067.79..39198.84 rows=1 width=447) (actual time=342.753..340932.286 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32067.65..39198.67 rows=1 width=421) (actual time=342.748..340896.132 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32067.23..39198.01 rows=1 width=279) (actual time=342.739..340821.987 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32067.09..39197.85 rows=1 width=276) (actual time=342.725..340775.031 rows=7359 loops=1)
Join Filter: (sh.share_holder_partner = partner.id)
Rows Removed by Join Filter: 204915707
-> Nested Loop Left Join (cost=28514.61..34092.46 rows=1 width=244) (actual time=287.323..610.192 rows=7359 loops=1)
-> Nested Loop Left Join (cost=28514.47..34092.30 rows=1 width=239) (actual time=287.318..573.234 rows=7359 loops=1)
-> Hash Right Join (cost=28513.48..34090.65 rows=1 width=159) (actual time=287.293..379.564 rows=7359 loops=1)
Hash Cond: (ws.contract_line = contractline.id)
-> Seq Scan on shareholder_web_subscription ws (cost=0.00..5378.84 rows=52884 width=24) (actual time=0.006..12.307 rows=52884 loops=1)
-> Hash (cost=28513.47..28513.47 rows=1 width=143) (actual time=287.243..287.243 rows=7359 loops=1)
Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1173kB
-> Nested Loop Left Join (cost=17456.16..28513.47 rows=1 width=143) (actual time=85.005..284.689 rows=7359 loops=1)
-> Nested Loop (cost=17456.03..28513.31 rows=1 width=148) (actual time=85.000..276.599 rows=7359 loops=1)
-> Nested Loop Left Join (cost=17455.73..28512.84 rows=1 width=148) (actual time=84.993..261.954 rows=7359 loops=1)
-> Nested Loop (cost=17455.60..28512.67 rows=1 width=140) (actual time=84.989..253.715 rows=7359 loops=1)
-> Nested Loop (cost=17455.18..28511.93 rows=1 width=93) (actual time=84.981..230.977 rows=7359 loops=1)
-> Merge Right Join (cost=17454.89..28511.52 rows=1 width=93) (actual time=84.974..211.200 rows=7359 loops=1)
Merge Cond: (subscribed_power.amendment = amendment.id)
-> GroupAggregate (cost=12457.78..22574.03 rows=75229 width=168) (actual time=57.500..175.674 rows=83432 loops=1)
Group Key: subscribed_power.amendment
-> Merge Join (cost=12457.78..20764.08 rows=173917 width=12) (actual time=57.479..129.530 rows=87938 loops=1)
Merge Cond: (subscribed_power.amendment = amendment_1.id)
-> Index Scan using contract_subscribed_power_amendment_idx on contract_subscribed_power subscribed_power (cost=0.42..13523.09 rows=173917 width=12) (actual time=0.009..33.704 rows=87963 loops=1)
-> Sort (cost=12457.36..12666.43 rows=83629 width=8) (actual time=57.467..67.071 rows=88019 loops=1)
Sort Key: amendment_1.id
Sort Method: quicksort Memory: 6988kB
-> Hash Join (cost=10.21..5619.97 rows=83629 width=8) (actual time=0.112..40.965 rows=83532 loops=1)
Hash Cond: (amendment_1.pricing = pricing.id)
-> Seq Scan on contract_amendment amendment_1 (cost=0.00..4460.29 rows=83629 width=16) (actual time=0.004..6.988 rows=83629 loops=1)
-> Hash (cost=8.43..8.43 rows=142 width=8) (actual time=0.095..0.095 rows=141 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 14kB
-> Hash Join (cost=1.07..8.43 rows=142 width=8) (actual time=0.012..0.078 rows=141 loops=1)
Hash Cond: (pricing.elec_range = elec_range.id)
-> Seq Scan on pricing_pricing pricing (cost=0.00..5.42 rows=142 width=16) (actual time=0.003..0.015 rows=142 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.006..0.006 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fluid_elec_range elec_range (cost=0.00..1.03 rows=3 width=8) (actual time=0.003..0.005 rows=3 loops=1)
-> Sort (cost=4997.11..4997.11 rows=1 width=69) (actual time=27.427..28.896 rows=7359 loops=1)
Sort Key: amendment.id
Sort Method: quicksort Memory: 1227kB
-> Nested Loop (cost=183.44..4997.10 rows=1 width=69) (actual time=1.115..24.616 rows=7359 loops=1)
-> Nested Loop (cost=183.15..4996.59 rows=1 width=49) (actual time=1.107..9.091 rows=7360 loops=1)
-> Index Scan using uk_3b1y5vw9gmh7u3jj8aa2uy0b9 on contact_partner businessprovider (cost=0.42..8.44 rows=1 width=13) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: ((business_provider_code)::text = 'BRZH'::text)
-> Bitmap Heap Scan on contract_contract_line contractline (cost=182.73..4907.58 rows=8057 width=52) (actual time=1.086..5.231 rows=7360 loops=1)
Recheck Cond: (business_provider_partner = businessprovider.id)
Heap Blocks: exact=3586
-> Bitmap Index Scan on contract_contract_line_business_provider_partner_idx (cost=0.00..180.72 rows=8057 width=0) (actual time=0.655..0.655 rows=7360 loops=1)
Index Cond: (business_provider_partner = businessprovider.id)
-> Index Scan using contract_amendment_pkey on contract_amendment amendment (cost=0.29..0.50 rows=1 width=28) (actual time=0.001..0.002 rows=1 loops=7360)
Index Cond: (id = contractline.amendment)
-> Index Scan using contract_contract_pkey on contract_contract contract (cost=0.29..0.40 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=7359)
Index Cond: (id = contractline.contract)
-> Index Scan using contact_partner_pkey on contact_partner partner (cost=0.42..0.74 rows=1 width=55) (actual time=0.002..0.002 rows=1 loops=7359)
Index Cond: (id = contract.main_client_partner)
-> Index Scan using contact_client_nature_pkey on contact_client_nature clientnature (cost=0.14..0.15 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=7359)
Index Cond: (id = partner.client_nature)
-> Index Scan using territory_mpt_pkey on territory_mpt mpt (cost=0.29..0.46 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=7359)
Index Cond: (id = contractline.mpt)
-> Index Scan using contract_user_segment_pkey on contract_user_segment usersegment (cost=0.14..0.15 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=7359)
Index Cond: (id = amendment.user_segment)
-> Nested Loop Left Join (cost=0.99..1.64 rows=1 width=96) (actual time=0.021..0.025 rows=1 loops=7359)
-> Nested Loop Left Join (cost=0.85..1.35 rows=1 width=89) (actual time=0.017..0.020 rows=1 loops=7359)
-> Nested Loop Left Join (cost=0.71..1.18 rows=1 width=76) (actual time=0.013..0.014 rows=1 loops=7359)
-> Index Scan using contact_address_pkey on contact_address a (cost=0.42..0.85 rows=1 width=84) (actual time=0.005..0.006 rows=1 loops=7359)
Index Cond: (mpt.address = id)
-> Index Scan using territory_commune_pkey on territory_commune commune (cost=0.29..0.32 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=7359)
Index Cond: (a.commune = id)
-> Index Scan using territory_department_pkey on territory_department dept (cost=0.14..0.16 rows=1 width=37) (actual time=0.003..0.004 rows=1 loops=7359)
Index Cond: (commune.department = id)
-> Index Scan using territory_region_pkey on territory_region reg (cost=0.14..0.27 rows=1 width=23) (actual time=0.003..0.003 rows=1 loops=7359)
Index Cond: (dept.region = id)
-> Index Scan using administration_status_pkey on administration_status status (cost=0.14..0.16 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=7359)
Index Cond: (id = contractline.status)
-> GroupAggregate (cost=3552.48..4479.27 rows=27827 width=80) (actual time=0.006..44.205 rows=27846 loops=7359)
Group Key: sh.share_holder_partner
-> Sort (cost=3552.48..3624.85 rows=28948 width=17) (actual time=0.003..2.913 rows=28946 loops=7359)
Sort Key: sh.share_holder_partner
Sort Method: quicksort Memory: 3030kB
-> Hash Join (cost=2.23..1407.26 rows=28948 width=17) (actual time=0.024..12.296 rows=28946 loops=1)
Hash Cond: (sh.company = sh_coop.id)
-> Seq Scan on shareholder_share_holder sh (cost=0.00..1007.00 rows=28948 width=20) (actual time=0.007..5.495 rows=28946 loops=1)
Filter: (nb_share > 0)
Rows Removed by Filter: 1934
-> Hash (cost=2.10..2.10 rows=10 width=13) (actual time=0.009..0.009 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on contact_company sh_coop (cost=0.00..2.10 rows=10 width=13) (actual time=0.003..0.006 rows=10 loops=1)
-> Index Scan using crm_origin_pkey on crm_origin co (cost=0.14..0.16 rows=1 width=19) (actual time=0.004..0.004 rows=1 loops=7359)
Index Cond: (id = ws.how_meet_enercoop)
-> Index Scan using contact_contact_pkey on contact_contact mc (cost=0.42..0.65 rows=1 width=150) (actual time=0.007..0.008 rows=1 loops=7359)
Index Cond: (partner.main_contact = id)
-> Index Scan using contact_title_pkey on contact_title title (cost=0.14..0.16 rows=1 width=42) (actual time=0.003..0.003 rows=1 loops=7359)
Index Cond: (mc.title = id)
-> Index Scan using contact_address_pkey on contact_address adr_contact (cost=0.43..0.70 rows=1 width=68) (actual time=0.005..0.005 rows=1 loops=7359)
Index Cond: (id = CASE WHEN (CASE WHEN ((partner.person_category_select)::text = 'naturalPerson'::text) THEN 'P'::text WHEN ((partner.person_category_select)::text = 'legalPerson'::text) THEN 'M'::text ELSE '?????'::text END = 'P'::text) THEN COALESCE(mc.address, mc.address_pro) ELSE COALESCE(mc.address_pro, mc.address) END)
-> Index Scan using contact_contact_address_contact_idx on contact_contact_address cca (cost=0.42..0.48 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=7359)
Index Cond: (contact = mc.id)
-> Index Scan using contact_contact_address_status_pkey on contact_contact_address_status npai (cost=0.13..0.15 rows=1 width=9) (actual time=0.000..0.000 rows=0 loops=7359)
Index Cond: (cca.contact_address_status = id)
-> Index Scan using crm_crm_request_original_contract_line_idx on crm_crm_request mesrequest (cost=0.28..0.29 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=7359)
Index Cond: (original_contract_line = contractline.id)
-> Index Scan using sale_product_sub_family_pkey on sale_product_sub_family mesproductsubfamily (cost=0.14..0.20 rows=1 width=62) (actual time=0.000..0.000 rows=0 loops=7359)
Index Cond: (id = mesrequest.product_sub_family)
Filter: (new_contract_ok IS TRUE)
Planning time: 21.106 ms
Execution time: 341275.027 ms
(118 lignes)
Attachments:
[text/plain] explain_where.txt (20.8K, ../../CAP-_FZEM9hDhE1ZatF7OQFxw0fHZakHZjDAYc=6t2zcRZfxxcw@mail.gmail.com/2-explain_where.txt)
download | inline:
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=39200.76..39200.76 rows=1 width=1066) (actual time=341273.300..341274.244 rows=7359 loops=1)
Sort Key: ((array_agg(subscribed_power.subscribed_power))[1]) DESC, status.name, contractline.id
Sort Method: quicksort Memory: 3930kB
-> Nested Loop Left Join (cost=32069.19..39200.75 rows=1 width=1066) (actual time=342.806..341203.151 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32069.05..39200.50 rows=1 width=508) (actual time=342.784..341102.848 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32068.77..39200.20 rows=1 width=500) (actual time=342.778..341070.310 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32068.64..39200.04 rows=1 width=507) (actual time=342.776..341058.256 rows=7359 loops=1)
Join Filter: (cca.address = adr_contact.id)
Rows Removed by Join Filter: 2254
-> Nested Loop Left Join (cost=32068.22..39199.55 rows=1 width=515) (actual time=342.767..340997.058 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32067.79..39198.84 rows=1 width=447) (actual time=342.753..340932.286 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32067.65..39198.67 rows=1 width=421) (actual time=342.748..340896.132 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32067.23..39198.01 rows=1 width=279) (actual time=342.739..340821.987 rows=7359 loops=1)
-> Nested Loop Left Join (cost=32067.09..39197.85 rows=1 width=276) (actual time=342.725..340775.031 rows=7359 loops=1)
Join Filter: (sh.share_holder_partner = partner.id)
Rows Removed by Join Filter: 204915707
-> Nested Loop Left Join (cost=28514.61..34092.46 rows=1 width=244) (actual time=287.323..610.192 rows=7359 loops=1)
-> Nested Loop Left Join (cost=28514.47..34092.30 rows=1 width=239) (actual time=287.318..573.234 rows=7359 loops=1)
-> Hash Right Join (cost=28513.48..34090.65 rows=1 width=159) (actual time=287.293..379.564 rows=7359 loops=1)
Hash Cond: (ws.contract_line = contractline.id)
-> Seq Scan on shareholder_web_subscription ws (cost=0.00..5378.84 rows=52884 width=24) (actual time=0.006..12.307 rows=52884 loops=1)
-> Hash (cost=28513.47..28513.47 rows=1 width=143) (actual time=287.243..287.243 rows=7359 loops=1)
Buckets: 8192 (originally 1024) Batches: 1 (originally 1) Memory Usage: 1173kB
-> Nested Loop Left Join (cost=17456.16..28513.47 rows=1 width=143) (actual time=85.005..284.689 rows=7359 loops=1)
-> Nested Loop (cost=17456.03..28513.31 rows=1 width=148) (actual time=85.000..276.599 rows=7359 loops=1)
-> Nested Loop Left Join (cost=17455.73..28512.84 rows=1 width=148) (actual time=84.993..261.954 rows=7359 loops=1)
-> Nested Loop (cost=17455.60..28512.67 rows=1 width=140) (actual time=84.989..253.715 rows=7359 loops=1)
-> Nested Loop (cost=17455.18..28511.93 rows=1 width=93) (actual time=84.981..230.977 rows=7359 loops=1)
-> Merge Right Join (cost=17454.89..28511.52 rows=1 width=93) (actual time=84.974..211.200 rows=7359 loops=1)
Merge Cond: (subscribed_power.amendment = amendment.id)
-> GroupAggregate (cost=12457.78..22574.03 rows=75229 width=168) (actual time=57.500..175.674 rows=83432 loops=1)
Group Key: subscribed_power.amendment
-> Merge Join (cost=12457.78..20764.08 rows=173917 width=12) (actual time=57.479..129.530 rows=87938 loops=1)
Merge Cond: (subscribed_power.amendment = amendment_1.id)
-> Index Scan using contract_subscribed_power_amendment_idx on contract_subscribed_power subscribed_power (cost=0.42..13523.09 rows=173917 width=12) (actual time=0.009..33.704 rows=87963 loops=1)
-> Sort (cost=12457.36..12666.43 rows=83629 width=8) (actual time=57.467..67.071 rows=88019 loops=1)
Sort Key: amendment_1.id
Sort Method: quicksort Memory: 6988kB
-> Hash Join (cost=10.21..5619.97 rows=83629 width=8) (actual time=0.112..40.965 rows=83532 loops=1)
Hash Cond: (amendment_1.pricing = pricing.id)
-> Seq Scan on contract_amendment amendment_1 (cost=0.00..4460.29 rows=83629 width=16) (actual time=0.004..6.988 rows=83629 loops=1)
-> Hash (cost=8.43..8.43 rows=142 width=8) (actual time=0.095..0.095 rows=141 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 14kB
-> Hash Join (cost=1.07..8.43 rows=142 width=8) (actual time=0.012..0.078 rows=141 loops=1)
Hash Cond: (pricing.elec_range = elec_range.id)
-> Seq Scan on pricing_pricing pricing (cost=0.00..5.42 rows=142 width=16) (actual time=0.003..0.015 rows=142 loops=1)
-> Hash (cost=1.03..1.03 rows=3 width=8) (actual time=0.006..0.006 rows=3 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on fluid_elec_range elec_range (cost=0.00..1.03 rows=3 width=8) (actual time=0.003..0.005 rows=3 loops=1)
-> Sort (cost=4997.11..4997.11 rows=1 width=69) (actual time=27.427..28.896 rows=7359 loops=1)
Sort Key: amendment.id
Sort Method: quicksort Memory: 1227kB
-> Nested Loop (cost=183.44..4997.10 rows=1 width=69) (actual time=1.115..24.616 rows=7359 loops=1)
-> Nested Loop (cost=183.15..4996.59 rows=1 width=49) (actual time=1.107..9.091 rows=7360 loops=1)
-> Index Scan using uk_3b1y5vw9gmh7u3jj8aa2uy0b9 on contact_partner businessprovider (cost=0.42..8.44 rows=1 width=13) (actual time=0.010..0.010 rows=1 loops=1)
Index Cond: ((business_provider_code)::text = 'BRZH'::text)
-> Bitmap Heap Scan on contract_contract_line contractline (cost=182.73..4907.58 rows=8057 width=52) (actual time=1.086..5.231 rows=7360 loops=1)
Recheck Cond: (business_provider_partner = businessprovider.id)
Heap Blocks: exact=3586
-> Bitmap Index Scan on contract_contract_line_business_provider_partner_idx (cost=0.00..180.72 rows=8057 width=0) (actual time=0.655..0.655 rows=7360 loops=1)
Index Cond: (business_provider_partner = businessprovider.id)
-> Index Scan using contract_amendment_pkey on contract_amendment amendment (cost=0.29..0.50 rows=1 width=28) (actual time=0.001..0.002 rows=1 loops=7360)
Index Cond: (id = contractline.amendment)
-> Index Scan using contract_contract_pkey on contract_contract contract (cost=0.29..0.40 rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=7359)
Index Cond: (id = contractline.contract)
-> Index Scan using contact_partner_pkey on contact_partner partner (cost=0.42..0.74 rows=1 width=55) (actual time=0.002..0.002 rows=1 loops=7359)
Index Cond: (id = contract.main_client_partner)
-> Index Scan using contact_client_nature_pkey on contact_client_nature clientnature (cost=0.14..0.15 rows=1 width=24) (actual time=0.001..0.001 rows=1 loops=7359)
Index Cond: (id = partner.client_nature)
-> Index Scan using territory_mpt_pkey on territory_mpt mpt (cost=0.29..0.46 rows=1 width=16) (actual time=0.001..0.001 rows=1 loops=7359)
Index Cond: (id = contractline.mpt)
-> Index Scan using contract_user_segment_pkey on contract_user_segment usersegment (cost=0.14..0.15 rows=1 width=11) (actual time=0.001..0.001 rows=1 loops=7359)
Index Cond: (id = amendment.user_segment)
-> Nested Loop Left Join (cost=0.99..1.64 rows=1 width=96) (actual time=0.021..0.025 rows=1 loops=7359)
-> Nested Loop Left Join (cost=0.85..1.35 rows=1 width=89) (actual time=0.017..0.020 rows=1 loops=7359)
-> Nested Loop Left Join (cost=0.71..1.18 rows=1 width=76) (actual time=0.013..0.014 rows=1 loops=7359)
-> Index Scan using contact_address_pkey on contact_address a (cost=0.42..0.85 rows=1 width=84) (actual time=0.005..0.006 rows=1 loops=7359)
Index Cond: (mpt.address = id)
-> Index Scan using territory_commune_pkey on territory_commune commune (cost=0.29..0.32 rows=1 width=16) (actual time=0.005..0.006 rows=1 loops=7359)
Index Cond: (a.commune = id)
-> Index Scan using territory_department_pkey on territory_department dept (cost=0.14..0.16 rows=1 width=37) (actual time=0.003..0.004 rows=1 loops=7359)
Index Cond: (commune.department = id)
-> Index Scan using territory_region_pkey on territory_region reg (cost=0.14..0.27 rows=1 width=23) (actual time=0.003..0.003 rows=1 loops=7359)
Index Cond: (dept.region = id)
-> Index Scan using administration_status_pkey on administration_status status (cost=0.14..0.16 rows=1 width=21) (actual time=0.003..0.003 rows=1 loops=7359)
Index Cond: (id = contractline.status)
-> GroupAggregate (cost=3552.48..4479.27 rows=27827 width=80) (actual time=0.006..44.205 rows=27846 loops=7359)
Group Key: sh.share_holder_partner
-> Sort (cost=3552.48..3624.85 rows=28948 width=17) (actual time=0.003..2.913 rows=28946 loops=7359)
Sort Key: sh.share_holder_partner
Sort Method: quicksort Memory: 3030kB
-> Hash Join (cost=2.23..1407.26 rows=28948 width=17) (actual time=0.024..12.296 rows=28946 loops=1)
Hash Cond: (sh.company = sh_coop.id)
-> Seq Scan on shareholder_share_holder sh (cost=0.00..1007.00 rows=28948 width=20) (actual time=0.007..5.495 rows=28946 loops=1)
Filter: (nb_share > 0)
Rows Removed by Filter: 1934
-> Hash (cost=2.10..2.10 rows=10 width=13) (actual time=0.009..0.009 rows=10 loops=1)
Buckets: 1024 Batches: 1 Memory Usage: 9kB
-> Seq Scan on contact_company sh_coop (cost=0.00..2.10 rows=10 width=13) (actual time=0.003..0.006 rows=10 loops=1)
-> Index Scan using crm_origin_pkey on crm_origin co (cost=0.14..0.16 rows=1 width=19) (actual time=0.004..0.004 rows=1 loops=7359)
Index Cond: (id = ws.how_meet_enercoop)
-> Index Scan using contact_contact_pkey on contact_contact mc (cost=0.42..0.65 rows=1 width=150) (actual time=0.007..0.008 rows=1 loops=7359)
Index Cond: (partner.main_contact = id)
-> Index Scan using contact_title_pkey on contact_title title (cost=0.14..0.16 rows=1 width=42) (actual time=0.003..0.003 rows=1 loops=7359)
Index Cond: (mc.title = id)
-> Index Scan using contact_address_pkey on contact_address adr_contact (cost=0.43..0.70 rows=1 width=68) (actual time=0.005..0.005 rows=1 loops=7359)
Index Cond: (id = CASE WHEN (CASE WHEN ((partner.person_category_select)::text = 'naturalPerson'::text) THEN 'P'::text WHEN ((partner.person_category_select)::text = 'legalPerson'::text) THEN 'M'::text ELSE '?????'::text END = 'P'::text) THEN COALESCE(mc.address, mc.address_pro) ELSE COALESCE(mc.address_pro, mc.address) END)
-> Index Scan using contact_contact_address_contact_idx on contact_contact_address cca (cost=0.42..0.48 rows=1 width=24) (actual time=0.006..0.006 rows=1 loops=7359)
Index Cond: (contact = mc.id)
-> Index Scan using contact_contact_address_status_pkey on contact_contact_address_status npai (cost=0.13..0.15 rows=1 width=9) (actual time=0.000..0.000 rows=0 loops=7359)
Index Cond: (cca.contact_address_status = id)
-> Index Scan using crm_crm_request_original_contract_line_idx on crm_crm_request mesrequest (cost=0.28..0.29 rows=1 width=16) (actual time=0.003..0.003 rows=0 loops=7359)
Index Cond: (original_contract_line = contractline.id)
-> Index Scan using sale_product_sub_family_pkey on sale_product_sub_family mesproductsubfamily (cost=0.14..0.20 rows=1 width=62) (actual time=0.000..0.000 rows=0 loops=7359)
Index Cond: (id = mesrequest.product_sub_family)
Filter: (new_contract_ok IS TRUE)
Planning time: 21.106 ms
Execution time: 341275.027 ms
(118 lignes)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bad plan
2018-01-23 12:03 Bad plan Laurent Martelli <martellilaurent@gmail.com>
2018-01-23 15:18 ` Re: Bad plan Justin Pryzby <pryzby@telsasoft.com>
2018-01-23 15:38 ` Re: Bad plan Laurent Martelli <martellilaurent@gmail.com>
@ 2018-01-23 15:59 ` Laurent Martelli <martellilaurent@gmail.com>
2018-01-23 17:18 ` Re: Bad plan Matthew Bellew <matthewb@labkey.com>
0 siblings, 1 reply; 19+ messages in thread
From: Laurent Martelli @ 2018-01-23 15:59 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
I've have a look to the plan with pgadmin, and I think the problem is
rather here :
-> Sort (cost=4997.11..4997.11 rows=1 width=69) (actual
time=27.427..28.896 rows=7359 loops=1)
Sort Key: amendment.id
Sort Method: quicksort Memory: 1227kB
-> Nested Loop (cost=183.44..4997.10 rows=1 width=69) (actual
time=1.115..24.616 rows=7359 loops=1)
-> Nested Loop (cost=183.15..4996.59 rows=1 width=49)
(actual time=1.107..9.091 rows=7360 loops=1)
-> Index Scan using uk_3b1y5vw9gmh7u3jj8aa2uy0b9 on
contact_partner businessprovider (cost=0.42..8.44 rows=1 width=13)
(actual time=0.010..0.010 rows=1 loops=1)
Index Cond: ((business_provider_code)::text =
'BRZH'::text)
-> Bitmap Heap Scan on contract_contract_line
contractline (cost=182.73..4907.58 rows=8057 width=52) (actual
time=1.086..5.231 rows=7360 loops=1)
Recheck Cond: (business_provider_partner =
businessprovider.id)
Heap Blocks: exact=3586
-> Bitmap Index Scan on
contract_contract_line_business_provider_partner_idx
(cost=0.00..180.72 rows=8057 width=0) (actual time=0.655..0.655
rows=7360 loops=1)
Index Cond: (business_provider_partner =
businessprovider.id)
-> Index Scan using contract_amendment_pkey on
contract_amendment amendment (cost=0.29..0.50 rows=1 width=28)
(actual time=0.001..0.002 rows=1 loops=7360)
Index Cond: (id = contractline.amendment)
The bitmap scan on contract_contract_line is good (8057 vs 7360 rows),
and so is the index scan (1 row), but the JOIN with "contact_partner
businessProvider" should give the 8057 rows from the bitmap scan,
shouldn't it ?
2018-01-23 16:38 GMT+01:00 Laurent Martelli <martellilaurent@gmail.com>:
> 2018-01-23 16:18 GMT+01:00 Justin Pryzby <pryzby@telsasoft.com>:
>> On Tue, Jan 23, 2018 at 01:03:49PM +0100, Laurent Martelli wrote:
>>
>>> Here is the default plan :
>>
>> Can you resend without line breaks or paste a link to explain.depesz?
>
> I hope it's better like that. I've attached it too, just in case.
>
>>
>> The problem appears to be here:
>>
>> -> Nested Loop Left Join (cost=32067.09..39197.85 rows=1 width=276) (actual time=342.725..340775.031 rows=7359 loops=1)
>> Join Filter: (sh.share_holder_partner = partner.id)
>> Rows Removed by Join Filter: 204915707
>>
>> Justin
>
>
>
> QUERY PLAN
> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Sort (cost=39200.76..39200.76 rows=1 width=1066) (actual
> time=341273.300..341274.244 rows=7359 loops=1)
> Sort Key: ((array_agg(subscribed_power.subscribed_power))[1]) DESC,
> status.name, contractline.id
> Sort Method: quicksort Memory: 3930kB
> -> Nested Loop Left Join (cost=32069.19..39200.75 rows=1
> width=1066) (actual time=342.806..341203.151 rows=7359 loops=1)
> -> Nested Loop Left Join (cost=32069.05..39200.50 rows=1
> width=508) (actual time=342.784..341102.848 rows=7359 loops=1)
> -> Nested Loop Left Join (cost=32068.77..39200.20
> rows=1 width=500) (actual time=342.778..341070.310 rows=7359 loops=1)
> -> Nested Loop Left Join
> (cost=32068.64..39200.04 rows=1 width=507) (actual
> time=342.776..341058.256 rows=7359 loops=1)
> Join Filter: (cca.address = adr_contact.id)
> Rows Removed by Join Filter: 2254
> -> Nested Loop Left Join
> (cost=32068.22..39199.55 rows=1 width=515) (actual
> time=342.767..340997.058 rows=7359 loops=1)
> -> Nested Loop Left Join
> (cost=32067.79..39198.84 rows=1 width=447) (actual
> time=342.753..340932.286 rows=7359 loops=1)
> -> Nested Loop Left Join
> (cost=32067.65..39198.67 rows=1 width=421) (actual
> time=342.748..340896.132 rows=7359 loops=1)
> -> Nested Loop Left Join
> (cost=32067.23..39198.01 rows=1 width=279) (actual
> time=342.739..340821.987 rows=7359 loops=1)
> -> Nested Loop
> Left Join (cost=32067.09..39197.85 rows=1 width=276) (actual
> time=342.725..340775.031 rows=7359 loops=1)
> Join Filter:
> (sh.share_holder_partner = partner.id)
> Rows Removed
> by Join Filter: 204915707
> -> Nested
> Loop Left Join (cost=28514.61..34092.46 rows=1 width=244) (actual
> time=287.323..610.192 rows=7359 loops=1)
> ->
> Nested Loop Left Join (cost=28514.47..34092.30 rows=1 width=239)
> (actual time=287.318..573.234 rows=7359 loops=1)
>
> -> Hash Right Join (cost=28513.48..34090.65 rows=1 width=159)
> (actual time=287.293..379.564 rows=7359 loops=1)
>
> Hash Cond: (ws.contract_line = contractline.id)
>
> -> Seq Scan on shareholder_web_subscription ws
> (cost=0.00..5378.84 rows=52884 width=24) (actual time=0.006..12.307
> rows=52884 loops=1)
>
> -> Hash (cost=28513.47..28513.47 rows=1 width=143) (actual
> time=287.243..287.243 rows=7359 loops=1)
>
> Buckets: 8192 (originally 1024) Batches: 1 (originally 1)
> Memory Usage: 1173kB
>
> -> Nested Loop Left Join (cost=17456.16..28513.47 rows=1
> width=143) (actual time=85.005..284.689 rows=7359 loops=1)
>
> -> Nested Loop (cost=17456.03..28513.31 rows=1
> width=148) (actual time=85.000..276.599 rows=7359 loops=1)
>
> -> Nested Loop Left Join
> (cost=17455.73..28512.84 rows=1 width=148) (actual
> time=84.993..261.954 rows=7359 loops=1)
>
> -> Nested Loop (cost=17455.60..28512.67
> rows=1 width=140) (actual time=84.989..253.715 rows=7359 loops=1)
>
> -> Nested Loop
> (cost=17455.18..28511.93 rows=1 width=93) (actual time=84.981..230.977
> rows=7359 loops=1)
>
> -> Merge Right Join
> (cost=17454.89..28511.52 rows=1 width=93) (actual time=84.974..211.200
> rows=7359 loops=1)
>
> Merge Cond:
> (subscribed_power.amendment = amendment.id)
>
> -> GroupAggregate
> (cost=12457.78..22574.03 rows=75229 width=168) (actual
> time=57.500..175.674 rows=83432 loops=1)
>
> Group Key:
> subscribed_power.amendment
>
> -> Merge Join
> (cost=12457.78..20764.08 rows=173917 width=12) (actual
> time=57.479..129.530 rows=87938 loops=1)
>
> Merge Cond:
> (subscribed_power.amendment = amendment_1.id)
>
> -> Index
> Scan using contract_subscribed_power_amendment_idx on
> contract_subscribed_power subscribed_power (cost=0.42..13523.09
> rows=173917 width=12) (actual time=0.009..33.704 rows=87963 loops=1)
>
> -> Sort
> (cost=12457.36..12666.43 rows=83629 width=8) (actual
> time=57.467..67.071 rows=88019 loops=1)
>
> Sort
> Key: amendment_1.id
>
> Sort
> Method: quicksort Memory: 6988kB
>
> ->
> Hash Join (cost=10.21..5619.97 rows=83629 width=8) (actual
> time=0.112..40.965 rows=83532 loops=1)
>
>
> Hash Cond: (amendment_1.pricing = pricing.id)
>
>
> -> Seq Scan on contract_amendment amendment_1 (cost=0.00..4460.29
> rows=83629 width=16) (actual time=0.004..6.988 rows=83629 loops=1)
>
>
> -> Hash (cost=8.43..8.43 rows=142 width=8) (actual time=0.095..0.095
> rows=141 loops=1)
>
>
> Buckets: 1024 Batches: 1 Memory Usage: 14kB
>
>
> -> Hash Join (cost=1.07..8.43 rows=142 width=8) (actual
> time=0.012..0.078 rows=141 loops=1)
>
>
> Hash Cond: (pricing.elec_range = elec_range.id)
>
>
> -> Seq Scan on pricing_pricing pricing (cost=0.00..5.42
> rows=142 width=16) (actual time=0.003..0.015 rows=142 loops=1)
>
>
> -> Hash (cost=1.03..1.03 rows=3 width=8) (actual
> time=0.006..0.006 rows=3 loops=1)
>
>
> Buckets: 1024 Batches: 1 Memory Usage: 9kB
>
>
> -> Seq Scan on fluid_elec_range elec_range
> (cost=0.00..1.03 rows=3 width=8) (actual time=0.003..0.005 rows=3
> loops=1)
>
> -> Sort
> (cost=4997.11..4997.11 rows=1 width=69) (actual time=27.427..28.896
> rows=7359 loops=1)
>
> Sort Key:
> amendment.id
>
> Sort Method:
> quicksort Memory: 1227kB
>
> -> Nested Loop
> (cost=183.44..4997.10 rows=1 width=69) (actual time=1.115..24.616
> rows=7359 loops=1)
>
> -> Nested
> Loop (cost=183.15..4996.59 rows=1 width=49) (actual time=1.107..9.091
> rows=7360 loops=1)
>
> ->
> Index Scan using uk_3b1y5vw9gmh7u3jj8aa2uy0b9 on contact_partner
> businessprovider (cost=0.42..8.44 rows=1 width=13) (actual
> time=0.010..0.010 rows=1 loops=1)
>
>
> Index Cond: ((business_provider_code)::text = 'BRZH'::text)
>
> ->
> Bitmap Heap Scan on contract_contract_line contractline
> (cost=182.73..4907.58 rows=8057 width=52) (actual time=1.086..5.231
> rows=7360 loops=1)
>
>
> Recheck Cond: (business_provider_partner = businessprovider.id)
>
>
> Heap Blocks: exact=3586
>
>
> -> Bitmap Index Scan on
> contract_contract_line_business_provider_partner_idx
> (cost=0.00..180.72 rows=8057 width=0) (actual time=0.655..0.655
> rows=7360 loops=1)
>
>
> Index Cond: (business_provider_partner = businessprovider.id)
>
> -> Index
> Scan using contract_amendment_pkey on contract_amendment amendment
> (cost=0.29..0.50 rows=1 width=28) (actual time=0.001..0.002 rows=1
> loops=7360)
>
> Index
> Cond: (id = contractline.amendment)
>
> -> Index Scan using
> contract_contract_pkey on contract_contract contract (cost=0.29..0.40
> rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=7359)
>
> Index Cond: (id =
> contractline.contract)
>
> -> Index Scan using
> contact_partner_pkey on contact_partner partner (cost=0.42..0.74
> rows=1 width=55) (actual time=0.002..0.002 rows=1 loops=7359)
>
> Index Cond: (id =
> contract.main_client_partner)
>
> -> Index Scan using
> contact_client_nature_pkey on contact_client_nature clientnature
> (cost=0.14..0.15 rows=1 width=24) (actual time=0.001..0.001 rows=1
> loops=7359)
>
> Index Cond: (id =
> partner.client_nature)
>
> -> Index Scan using territory_mpt_pkey on
> territory_mpt mpt (cost=0.29..0.46 rows=1 width=16) (actual
> time=0.001..0.001 rows=1 loops=7359)
>
> Index Cond: (id = contractline.mpt)
>
> -> Index Scan using contract_user_segment_pkey on
> contract_user_segment usersegment (cost=0.14..0.15 rows=1 width=11)
> (actual time=0.001..0.001 rows=1 loops=7359)
>
> Index Cond: (id = amendment.user_segment)
>
> -> Nested Loop Left Join (cost=0.99..1.64 rows=1 width=96) (actual
> time=0.021..0.025 rows=1 loops=7359)
>
> -> Nested Loop Left Join (cost=0.85..1.35 rows=1 width=89)
> (actual time=0.017..0.020 rows=1 loops=7359)
>
> -> Nested Loop Left Join (cost=0.71..1.18 rows=1 width=76)
> (actual time=0.013..0.014 rows=1 loops=7359)
>
> -> Index Scan using contact_address_pkey on
> contact_address a (cost=0.42..0.85 rows=1 width=84) (actual
> time=0.005..0.006 rows=1 loops=7359)
>
> Index Cond: (mpt.address = id)
>
> -> Index Scan using territory_commune_pkey on
> territory_commune commune (cost=0.29..0.32 rows=1 width=16) (actual
> time=0.005..0.006 rows=1 loops=7359)
>
> Index Cond: (a.commune = id)
>
> -> Index Scan using territory_department_pkey on
> territory_department dept (cost=0.14..0.16 rows=1 width=37) (actual
> time=0.003..0.004 rows=1 loops=7359)
>
> Index Cond: (commune.department = id)
>
> -> Index Scan using territory_region_pkey on territory_region reg
> (cost=0.14..0.27 rows=1 width=23) (actual time=0.003..0.003 rows=1
> loops=7359)
>
> Index Cond: (dept.region = id)
> ->
> Index Scan using administration_status_pkey on administration_status
> status (cost=0.14..0.16 rows=1 width=21) (actual time=0.003..0.003
> rows=1 loops=7359)
>
> Index Cond: (id = contractline.status)
> ->
> GroupAggregate (cost=3552.48..4479.27 rows=27827 width=80) (actual
> time=0.006..44.205 rows=27846 loops=7359)
> Group
> Key: sh.share_holder_partner
> ->
> Sort (cost=3552.48..3624.85 rows=28948 width=17) (actual
> time=0.003..2.913 rows=28946 loops=7359)
>
> Sort Key: sh.share_holder_partner
>
> Sort Method: quicksort Memory: 3030kB
>
> -> Hash Join (cost=2.23..1407.26 rows=28948 width=17) (actual
> time=0.024..12.296 rows=28946 loops=1)
>
> Hash Cond: (sh.company = sh_coop.id)
>
> -> Seq Scan on shareholder_share_holder sh (cost=0.00..1007.00
> rows=28948 width=20) (actual time=0.007..5.495 rows=28946 loops=1)
>
> Filter: (nb_share > 0)
>
> Rows Removed by Filter: 1934
>
> -> Hash (cost=2.10..2.10 rows=10 width=13) (actual
> time=0.009..0.009 rows=10 loops=1)
>
> Buckets: 1024 Batches: 1 Memory Usage: 9kB
>
> -> Seq Scan on contact_company sh_coop (cost=0.00..2.10
> rows=10 width=13) (actual time=0.003..0.006 rows=10 loops=1)
> -> Index Scan
> using crm_origin_pkey on crm_origin co (cost=0.14..0.16 rows=1
> width=19) (actual time=0.004..0.004 rows=1 loops=7359)
> Index Cond:
> (id = ws.how_meet_enercoop)
> -> Index Scan using
> contact_contact_pkey on contact_contact mc (cost=0.42..0.65 rows=1
> width=150) (actual time=0.007..0.008 rows=1 loops=7359)
> Index Cond:
> (partner.main_contact = id)
> -> Index Scan using
> contact_title_pkey on contact_title title (cost=0.14..0.16 rows=1
> width=42) (actual time=0.003..0.003 rows=1 loops=7359)
> Index Cond: (mc.title = id)
> -> Index Scan using
> contact_address_pkey on contact_address adr_contact (cost=0.43..0.70
> rows=1 width=68) (actual time=0.005..0.005 rows=1 loops=7359)
> Index Cond: (id = CASE WHEN
> (CASE WHEN ((partner.person_category_select)::text =
> 'naturalPerson'::text) THEN 'P'::text WHEN
> ((partner.person_category_select)::text = 'legalPerson'::text) THEN
> 'M'::text ELSE '?????'::text END = 'P'::text) THEN
> COALESCE(mc.address, mc.address_pro) ELSE COALESCE(mc.address_pro,
> mc.address) END)
> -> Index Scan using
> contact_contact_address_contact_idx on contact_contact_address cca
> (cost=0.42..0.48 rows=1 width=24) (actual time=0.006..0.006 rows=1
> loops=7359)
> Index Cond: (contact = mc.id)
> -> Index Scan using
> contact_contact_address_status_pkey on contact_contact_address_status
> npai (cost=0.13..0.15 rows=1 width=9) (actual time=0.000..0.000
> rows=0 loops=7359)
> Index Cond: (cca.contact_address_status = id)
> -> Index Scan using
> crm_crm_request_original_contract_line_idx on crm_crm_request
> mesrequest (cost=0.28..0.29 rows=1 width=16) (actual
> time=0.003..0.003 rows=0 loops=7359)
> Index Cond: (original_contract_line = contractline.id)
> -> Index Scan using sale_product_sub_family_pkey on
> sale_product_sub_family mesproductsubfamily (cost=0.14..0.20 rows=1
> width=62) (actual time=0.000..0.000 rows=0 loops=7359)
> Index Cond: (id = mesrequest.product_sub_family)
> Filter: (new_contract_ok IS TRUE)
> Planning time: 21.106 ms
> Execution time: 341275.027 ms
> (118 lignes)
>
>
> --
> http://www.laurentmartelli.com // http://www.imprimart.fr
--
http://www.laurentmartelli.com // http://www.imprimart.fr
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Bad plan
2018-01-23 12:03 Bad plan Laurent Martelli <martellilaurent@gmail.com>
2018-01-23 15:18 ` Re: Bad plan Justin Pryzby <pryzby@telsasoft.com>
2018-01-23 15:38 ` Re: Bad plan Laurent Martelli <martellilaurent@gmail.com>
2018-01-23 15:59 ` Re: Bad plan Laurent Martelli <martellilaurent@gmail.com>
@ 2018-01-23 17:18 ` Matthew Bellew <matthewb@labkey.com>
0 siblings, 0 replies; 19+ messages in thread
From: Matthew Bellew @ 2018-01-23 17:18 UTC (permalink / raw)
To: pgsql-performance@lists.postgresql.org
In my opinion this is the Achilles heel of the postgres optimizer. Row
estimates should never return 1, unless the estimate is provably <=1. This
is particularly a problem with join estimates. A dumb fix for this is to
change clamp_join_row_est() to never return a value <2. This fixes most of
my observed poor plans. The real fix is to track uniqueness (or provable
max rows) along with the selectivity estimate.
Here's the dumb fix.
https://github.com/labkey-matthewb/postgres/commit/b1fd99f4deffbbf3db2172ccaba51a34f18d1b1a
On Tue, Jan 23, 2018 at 7:59 AM, Laurent Martelli <martellilaurent@gmail.com
> wrote:
> I've have a look to the plan with pgadmin, and I think the problem is
> rather here :
>
> -> Sort (cost=4997.11..4997.11 rows=1 width=69) (actual
> time=27.427..28.896 rows=7359 loops=1)
> Sort Key: amendment.id
> Sort Method: quicksort Memory: 1227kB
> -> Nested Loop (cost=183.44..4997.10 rows=1 width=69) (actual
> time=1.115..24.616 rows=7359 loops=1)
> -> Nested Loop (cost=183.15..4996.59 rows=1 width=49)
> (actual time=1.107..9.091 rows=7360 loops=1)
> -> Index Scan using uk_3b1y5vw9gmh7u3jj8aa2uy0b9 on
> contact_partner businessprovider (cost=0.42..8.44 rows=1 width=13)
> (actual time=0.010..0.010 rows=1 loops=1)
> Index Cond: ((business_provider_code)::text =
> 'BRZH'::text)
> -> Bitmap Heap Scan on contract_contract_line
> contractline (cost=182.73..4907.58 rows=8057 width=52) (actual
> time=1.086..5.231 rows=7360 loops=1)
> Recheck Cond: (business_provider_partner =
> businessprovider.id)
> Heap Blocks: exact=3586
> -> Bitmap Index Scan on
> contract_contract_line_business_provider_partner_idx
> (cost=0.00..180.72 rows=8057 width=0) (actual time=0.655..0.655
> rows=7360 loops=1)
> Index Cond: (business_provider_partner =
> businessprovider.id)
> -> Index Scan using contract_amendment_pkey on
> contract_amendment amendment (cost=0.29..0.50 rows=1 width=28)
> (actual time=0.001..0.002 rows=1 loops=7360)
> Index Cond: (id = contractline.amendment)
>
> The bitmap scan on contract_contract_line is good (8057 vs 7360 rows),
> and so is the index scan (1 row), but the JOIN with "contact_partner
> businessProvider" should give the 8057 rows from the bitmap scan,
> shouldn't it ?
>
>
> 2018-01-23 16:38 GMT+01:00 Laurent Martelli <martellilaurent@gmail.com>:
> > 2018-01-23 16:18 GMT+01:00 Justin Pryzby <pryzby@telsasoft.com>:
> >> On Tue, Jan 23, 2018 at 01:03:49PM +0100, Laurent Martelli wrote:
> >>
> >>> Here is the default plan :
> >>
> >> Can you resend without line breaks or paste a link to explain.depesz?
> >
> > I hope it's better like that. I've attached it too, just in case.
> >
> >>
> >> The problem appears to be here:
> >>
> >> -> Nested Loop Left Join (cost=32067.09..39197.85 rows=1 width=276)
> (actual time=342.725..340775.031 rows=7359 loops=1)
> >> Join Filter: (sh.share_holder_partner = partner.id)
> >> Rows Removed by Join Filter: 204915707
> >>
> >> Justin
> >
> >
> >
> > QUERY PLAN
> > ------------------------------------------------------------
> ------------------------------------------------------------
> ------------------------------------------------------------
> ------------------------------------------------------------
> ------------------------------------------------------------
> ------------------------------------------------------------------
> > Sort (cost=39200.76..39200.76 rows=1 width=1066) (actual
> > time=341273.300..341274.244 rows=7359 loops=1)
> > Sort Key: ((array_agg(subscribed_power.subscribed_power))[1]) DESC,
> > status.name, contractline.id
> > Sort Method: quicksort Memory: 3930kB
> > -> Nested Loop Left Join (cost=32069.19..39200.75 rows=1
> > width=1066) (actual time=342.806..341203.151 rows=7359 loops=1)
> > -> Nested Loop Left Join (cost=32069.05..39200.50 rows=1
> > width=508) (actual time=342.784..341102.848 rows=7359 loops=1)
> > -> Nested Loop Left Join (cost=32068.77..39200.20
> > rows=1 width=500) (actual time=342.778..341070.310 rows=7359 loops=1)
> > -> Nested Loop Left Join
> > (cost=32068.64..39200.04 rows=1 width=507) (actual
> > time=342.776..341058.256 rows=7359 loops=1)
> > Join Filter: (cca.address = adr_contact.id)
> > Rows Removed by Join Filter: 2254
> > -> Nested Loop Left Join
> > (cost=32068.22..39199.55 rows=1 width=515) (actual
> > time=342.767..340997.058 rows=7359 loops=1)
> > -> Nested Loop Left Join
> > (cost=32067.79..39198.84 rows=1 width=447) (actual
> > time=342.753..340932.286 rows=7359 loops=1)
> > -> Nested Loop Left Join
> > (cost=32067.65..39198.67 rows=1 width=421) (actual
> > time=342.748..340896.132 rows=7359 loops=1)
> > -> Nested Loop Left Join
> > (cost=32067.23..39198.01 rows=1 width=279) (actual
> > time=342.739..340821.987 rows=7359 loops=1)
> > -> Nested Loop
> > Left Join (cost=32067.09..39197.85 rows=1 width=276) (actual
> > time=342.725..340775.031 rows=7359 loops=1)
> > Join Filter:
> > (sh.share_holder_partner = partner.id)
> > Rows Removed
> > by Join Filter: 204915707
> > -> Nested
> > Loop Left Join (cost=28514.61..34092.46 rows=1 width=244) (actual
> > time=287.323..610.192 rows=7359 loops=1)
> > ->
> > Nested Loop Left Join (cost=28514.47..34092.30 rows=1 width=239)
> > (actual time=287.318..573.234 rows=7359 loops=1)
> >
> > -> Hash Right Join (cost=28513.48..34090.65 rows=1 width=159)
> > (actual time=287.293..379.564 rows=7359 loops=1)
> >
> > Hash Cond: (ws.contract_line = contractline.id)
> >
> > -> Seq Scan on shareholder_web_subscription ws
> > (cost=0.00..5378.84 rows=52884 width=24) (actual time=0.006..12.307
> > rows=52884 loops=1)
> >
> > -> Hash (cost=28513.47..28513.47 rows=1 width=143) (actual
> > time=287.243..287.243 rows=7359 loops=1)
> >
> > Buckets: 8192 (originally 1024) Batches: 1 (originally 1)
> > Memory Usage: 1173kB
> >
> > -> Nested Loop Left Join (cost=17456.16..28513.47 rows=1
> > width=143) (actual time=85.005..284.689 rows=7359 loops=1)
> >
> > -> Nested Loop (cost=17456.03..28513.31 rows=1
> > width=148) (actual time=85.000..276.599 rows=7359 loops=1)
> >
> > -> Nested Loop Left Join
> > (cost=17455.73..28512.84 rows=1 width=148) (actual
> > time=84.993..261.954 rows=7359 loops=1)
> >
> > -> Nested Loop (cost=17455.60..28512.67
> > rows=1 width=140) (actual time=84.989..253.715 rows=7359 loops=1)
> >
> > -> Nested Loop
> > (cost=17455.18..28511.93 rows=1 width=93) (actual time=84.981..230.977
> > rows=7359 loops=1)
> >
> > -> Merge Right Join
> > (cost=17454.89..28511.52 rows=1 width=93) (actual time=84.974..211.200
> > rows=7359 loops=1)
> >
> > Merge Cond:
> > (subscribed_power.amendment = amendment.id)
> >
> > -> GroupAggregate
> > (cost=12457.78..22574.03 rows=75229 width=168) (actual
> > time=57.500..175.674 rows=83432 loops=1)
> >
> > Group Key:
> > subscribed_power.amendment
> >
> > -> Merge Join
> > (cost=12457.78..20764.08 rows=173917 width=12) (actual
> > time=57.479..129.530 rows=87938 loops=1)
> >
> > Merge Cond:
> > (subscribed_power.amendment = amendment_1.id)
> >
> > -> Index
> > Scan using contract_subscribed_power_amendment_idx on
> > contract_subscribed_power subscribed_power (cost=0.42..13523.09
> > rows=173917 width=12) (actual time=0.009..33.704 rows=87963 loops=1)
> >
> > -> Sort
> > (cost=12457.36..12666.43 rows=83629 width=8) (actual
> > time=57.467..67.071 rows=88019 loops=1)
> >
> > Sort
> > Key: amendment_1.id
> >
> > Sort
> > Method: quicksort Memory: 6988kB
> >
> > ->
> > Hash Join (cost=10.21..5619.97 rows=83629 width=8) (actual
> > time=0.112..40.965 rows=83532 loops=1)
> >
> >
> > Hash Cond: (amendment_1.pricing = pricing.id)
> >
> >
> > -> Seq Scan on contract_amendment amendment_1 (cost=0.00..4460.29
> > rows=83629 width=16) (actual time=0.004..6.988 rows=83629 loops=1)
> >
> >
> > -> Hash (cost=8.43..8.43 rows=142 width=8) (actual time=0.095..0.095
> > rows=141 loops=1)
> >
> >
> > Buckets: 1024 Batches: 1 Memory Usage: 14kB
> >
> >
> > -> Hash Join (cost=1.07..8.43 rows=142 width=8) (actual
> > time=0.012..0.078 rows=141 loops=1)
> >
> >
> > Hash Cond: (pricing.elec_range = elec_range.id)
> >
> >
> > -> Seq Scan on pricing_pricing pricing (cost=0.00..5.42
> > rows=142 width=16) (actual time=0.003..0.015 rows=142 loops=1)
> >
> >
> > -> Hash (cost=1.03..1.03 rows=3 width=8) (actual
> > time=0.006..0.006 rows=3 loops=1)
> >
> >
> > Buckets: 1024 Batches: 1 Memory Usage: 9kB
> >
> >
> > -> Seq Scan on fluid_elec_range elec_range
> > (cost=0.00..1.03 rows=3 width=8) (actual time=0.003..0.005 rows=3
> > loops=1)
> >
> > -> Sort
> > (cost=4997.11..4997.11 rows=1 width=69) (actual time=27.427..28.896
> > rows=7359 loops=1)
> >
> > Sort Key:
> > amendment.id
> >
> > Sort Method:
> > quicksort Memory: 1227kB
> >
> > -> Nested Loop
> > (cost=183.44..4997.10 rows=1 width=69) (actual time=1.115..24.616
> > rows=7359 loops=1)
> >
> > -> Nested
> > Loop (cost=183.15..4996.59 rows=1 width=49) (actual time=1.107..9.091
> > rows=7360 loops=1)
> >
> > ->
> > Index Scan using uk_3b1y5vw9gmh7u3jj8aa2uy0b9 on contact_partner
> > businessprovider (cost=0.42..8.44 rows=1 width=13) (actual
> > time=0.010..0.010 rows=1 loops=1)
> >
> >
> > Index Cond: ((business_provider_code)::text = 'BRZH'::text)
> >
> > ->
> > Bitmap Heap Scan on contract_contract_line contractline
> > (cost=182.73..4907.58 rows=8057 width=52) (actual time=1.086..5.231
> > rows=7360 loops=1)
> >
> >
> > Recheck Cond: (business_provider_partner = businessprovider.id)
> >
> >
> > Heap Blocks: exact=3586
> >
> >
> > -> Bitmap Index Scan on
> > contract_contract_line_business_provider_partner_idx
> > (cost=0.00..180.72 rows=8057 width=0) (actual time=0.655..0.655
> > rows=7360 loops=1)
> >
> >
> > Index Cond: (business_provider_partner = businessprovider.id)
> >
> > -> Index
> > Scan using contract_amendment_pkey on contract_amendment amendment
> > (cost=0.29..0.50 rows=1 width=28) (actual time=0.001..0.002 rows=1
> > loops=7360)
> >
> > Index
> > Cond: (id = contractline.amendment)
> >
> > -> Index Scan using
> > contract_contract_pkey on contract_contract contract (cost=0.29..0.40
> > rows=1 width=24) (actual time=0.002..0.002 rows=1 loops=7359)
> >
> > Index Cond: (id =
> > contractline.contract)
> >
> > -> Index Scan using
> > contact_partner_pkey on contact_partner partner (cost=0.42..0.74
> > rows=1 width=55) (actual time=0.002..0.002 rows=1 loops=7359)
> >
> > Index Cond: (id =
> > contract.main_client_partner)
> >
> > -> Index Scan using
> > contact_client_nature_pkey on contact_client_nature clientnature
> > (cost=0.14..0.15 rows=1 width=24) (actual time=0.001..0.001 rows=1
> > loops=7359)
> >
> > Index Cond: (id =
> > partner.client_nature)
> >
> > -> Index Scan using territory_mpt_pkey on
> > territory_mpt mpt (cost=0.29..0.46 rows=1 width=16) (actual
> > time=0.001..0.001 rows=1 loops=7359)
> >
> > Index Cond: (id = contractline.mpt)
> >
> > -> Index Scan using contract_user_segment_pkey on
> > contract_user_segment usersegment (cost=0.14..0.15 rows=1 width=11)
> > (actual time=0.001..0.001 rows=1 loops=7359)
> >
> > Index Cond: (id = amendment.user_segment)
> >
> > -> Nested Loop Left Join (cost=0.99..1.64 rows=1 width=96) (actual
> > time=0.021..0.025 rows=1 loops=7359)
> >
> > -> Nested Loop Left Join (cost=0.85..1.35 rows=1 width=89)
> > (actual time=0.017..0.020 rows=1 loops=7359)
> >
> > -> Nested Loop Left Join (cost=0.71..1.18 rows=1 width=76)
> > (actual time=0.013..0.014 rows=1 loops=7359)
> >
> > -> Index Scan using contact_address_pkey on
> > contact_address a (cost=0.42..0.85 rows=1 width=84) (actual
> > time=0.005..0.006 rows=1 loops=7359)
> >
> > Index Cond: (mpt.address = id)
> >
> > -> Index Scan using territory_commune_pkey on
> > territory_commune commune (cost=0.29..0.32 rows=1 width=16) (actual
> > time=0.005..0.006 rows=1 loops=7359)
> >
> > Index Cond: (a.commune = id)
> >
> > -> Index Scan using territory_department_pkey on
> > territory_department dept (cost=0.14..0.16 rows=1 width=37) (actual
> > time=0.003..0.004 rows=1 loops=7359)
> >
> > Index Cond: (commune.department = id)
> >
> > -> Index Scan using territory_region_pkey on territory_region reg
> > (cost=0.14..0.27 rows=1 width=23) (actual time=0.003..0.003 rows=1
> > loops=7359)
> >
> > Index Cond: (dept.region = id)
> > ->
> > Index Scan using administration_status_pkey on administration_status
> > status (cost=0.14..0.16 rows=1 width=21) (actual time=0.003..0.003
> > rows=1 loops=7359)
> >
> > Index Cond: (id = contractline.status)
> > ->
> > GroupAggregate (cost=3552.48..4479.27 rows=27827 width=80) (actual
> > time=0.006..44.205 rows=27846 loops=7359)
> > Group
> > Key: sh.share_holder_partner
> > ->
> > Sort (cost=3552.48..3624.85 rows=28948 width=17) (actual
> > time=0.003..2.913 rows=28946 loops=7359)
> >
> > Sort Key: sh.share_holder_partner
> >
> > Sort Method: quicksort Memory: 3030kB
> >
> > -> Hash Join (cost=2.23..1407.26 rows=28948 width=17) (actual
> > time=0.024..12.296 rows=28946 loops=1)
> >
> > Hash Cond: (sh.company = sh_coop.id)
> >
> > -> Seq Scan on shareholder_share_holder sh (cost=0.00..1007.00
> > rows=28948 width=20) (actual time=0.007..5.495 rows=28946 loops=1)
> >
> > Filter: (nb_share > 0)
> >
> > Rows Removed by Filter: 1934
> >
> > -> Hash (cost=2.10..2.10 rows=10 width=13) (actual
> > time=0.009..0.009 rows=10 loops=1)
> >
> > Buckets: 1024 Batches: 1 Memory Usage: 9kB
> >
> > -> Seq Scan on contact_company sh_coop (cost=0.00..2.10
> > rows=10 width=13) (actual time=0.003..0.006 rows=10 loops=1)
> > -> Index Scan
> > using crm_origin_pkey on crm_origin co (cost=0.14..0.16 rows=1
> > width=19) (actual time=0.004..0.004 rows=1 loops=7359)
> > Index Cond:
> > (id = ws.how_meet_enercoop)
> > -> Index Scan using
> > contact_contact_pkey on contact_contact mc (cost=0.42..0.65 rows=1
> > width=150) (actual time=0.007..0.008 rows=1 loops=7359)
> > Index Cond:
> > (partner.main_contact = id)
> > -> Index Scan using
> > contact_title_pkey on contact_title title (cost=0.14..0.16 rows=1
> > width=42) (actual time=0.003..0.003 rows=1 loops=7359)
> > Index Cond: (mc.title = id)
> > -> Index Scan using
> > contact_address_pkey on contact_address adr_contact (cost=0.43..0.70
> > rows=1 width=68) (actual time=0.005..0.005 rows=1 loops=7359)
> > Index Cond: (id = CASE WHEN
> > (CASE WHEN ((partner.person_category_select)::text =
> > 'naturalPerson'::text) THEN 'P'::text WHEN
> > ((partner.person_category_select)::text = 'legalPerson'::text) THEN
> > 'M'::text ELSE '?????'::text END = 'P'::text) THEN
> > COALESCE(mc.address, mc.address_pro) ELSE COALESCE(mc.address_pro,
> > mc.address) END)
> > -> Index Scan using
> > contact_contact_address_contact_idx on contact_contact_address cca
> > (cost=0.42..0.48 rows=1 width=24) (actual time=0.006..0.006 rows=1
> > loops=7359)
> > Index Cond: (contact = mc.id)
> > -> Index Scan using
> > contact_contact_address_status_pkey on contact_contact_address_status
> > npai (cost=0.13..0.15 rows=1 width=9) (actual time=0.000..0.000
> > rows=0 loops=7359)
> > Index Cond: (cca.contact_address_status = id)
> > -> Index Scan using
> > crm_crm_request_original_contract_line_idx on crm_crm_request
> > mesrequest (cost=0.28..0.29 rows=1 width=16) (actual
> > time=0.003..0.003 rows=0 loops=7359)
> > Index Cond: (original_contract_line =
> contractline.id)
> > -> Index Scan using sale_product_sub_family_pkey on
> > sale_product_sub_family mesproductsubfamily (cost=0.14..0.20 rows=1
> > width=62) (actual time=0.000..0.000 rows=0 loops=7359)
> > Index Cond: (id = mesrequest.product_sub_family)
> > Filter: (new_contract_ok IS TRUE)
> > Planning time: 21.106 ms
> > Execution time: 341275.027 ms
> > (118 lignes)
> >
> >
> > --
> > http://www.laurentmartelli.com // http://www.imprimart.fr
>
>
>
> --
> http://www.laurentmartelli.com // http://www.imprimart.fr
>
>
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2018-01-23 17:18 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-08 10:32 bad plan Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 11:39 ` Richard Huxton <dev@archonet.com>
2005-03-08 12:22 ` Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 13:20 ` Richard Huxton <dev@archonet.com>
2005-03-08 18:35 ` Gaetano Mendola <mendola@bigfoot.com>
2005-03-08 19:20 ` Richard Huxton <dev@archonet.com>
2005-03-08 20:01 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-08 20:25 ` Richard Huxton <dev@archonet.com>
2005-03-08 20:42 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-03-09 00:47 ` Gaetano Mendola <mendola@bigfoot.com>
2012-04-05 11:47 bad plan Julien Cigar <jcigar@ulb.ac.be>
2012-04-05 14:26 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
2012-04-05 19:47 ` Ants Aasma <ants@cybertec.at>
2012-04-06 11:16 ` Julien Cigar <jcigar@ulb.ac.be>
2018-01-23 12:03 Bad plan Laurent Martelli <martellilaurent@gmail.com>
2018-01-23 15:18 ` Re: Bad plan Justin Pryzby <pryzby@telsasoft.com>
2018-01-23 15:38 ` Re: Bad plan Laurent Martelli <martellilaurent@gmail.com>
2018-01-23 15:59 ` Re: Bad plan Laurent Martelli <martellilaurent@gmail.com>
2018-01-23 17:18 ` Re: Bad plan Matthew Bellew <matthewb@labkey.com>
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