David Brown wrote:
> Gaetano Mendola wrote:
>
>> I think is due the fact that first queries were performed in peakhours.
>>
>>
> A memory intensive operation takes 7.5 times longer during heavy loads.
> Doesn't this suggest that swapping of working memory is occurring?
>

I'll check next time, do you know how to detect it ?

> If you have time, could you try:

> 1. Disabling Mergejoin as well as Hashjoin. *If* I'm right, it may be even faster.
> 2. Changing the selection criteria so that sat_request returns a row. I suspect it will still be faster without the hash join.

For the 1. these are the result:

empdb=# explain analyze SELECT id_sat_request
empdb-# FROM sat_request sr,
empdb-# v_sc_packages vs
empdb-# WHERE sr.id_package = vs.id_package AND
empdb-# id_user = 29416 AND
empdb-# id_url = 329268 AND
empdb-# vs.estimated_start > now() AND
empdb-# id_sat_request_status = sp_lookup_id('sat_request_status', 'Scheduled');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=316.48..322.94 rows=1 width=4) (actual time=1251.762..1251.762 rows=0 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Subquery Scan vs (cost=313.43..317.31 rows=517 width=4) (actual time=1249.052..1250.479 rows=662 loops=1)
-> Sort (cost=313.43..314.72 rows=517 width=263) (actual time=1249.047..1249.465 rows=662 loops=1)
Sort Key: vs.estimated_start
-> Hash Join (cost=246.24..290.13 rows=517 width=263) (actual time=1226.364..1245.202 rows=662 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Subquery Scan vpk (cost=145.12..153.42 rows=1106 width=218) (actual time=1188.338..1198.109 rows=1119 loops=1)
-> Sort (cost=145.12..147.89 rows=1106 width=162) (actual time=1188.316..1189.231 rows=1119 loops=1)
Sort Key: p.id_publisher, p.name
-> Hash Left Join (cost=15.45..89.21 rows=1106 width=162) (actual time=5.014..1168.816 rows=1119 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Seq Scan on packages p (cost=0.00..53.53 rows=1106 width=146) (actual time=0.021..4.561 rows=1119 loops=1)
-> Hash (cost=13.63..13.63 rows=727 width=20) (actual time=3.742..3.742 rows=0 loops=1)
-> Seq Scan on package_security ps (cost=0.00..13.63 rows=727 width=20) (actual time=0.035..2.461 rows=757 loops=1)
-> Hash (cost=100.88..100.88 rows=94 width=49) (actual time=37.687..37.687 rows=0 loops=1)
-> Merge Join (cost=96.28..100.88 rows=94 width=49) (actual time=30.023..36.571 rows=662 loops=1)
Merge Cond: ("outer".id_program = "inner".id_program)
-> Subquery Scan vs (cost=84.36..87.10 rows=366 width=16) (actual time=13.020..15.507 rows=662 loops=1)
-> Sort (cost=84.36..85.27 rows=366 width=20) (actual time=13.009..13.613 rows=662 loops=1)
Sort Key: sequences.id_program, sequences.internal_position
-> Seq Scan on sequences (cost=0.00..68.78 rows=366 width=20) (actual time=0.658..7.630 rows=662 loops=1)
Filter: ((estimated_start IS NOT NULL) AND (date_trunc('seconds'::text, estimated_start) > now()))
-> Sort (cost=11.92..12.05 rows=51 width=37) (actual time=16.981..17.596 rows=698 loops=1)
Sort Key: vpr.id_program
-> Subquery Scan vpr (cost=10.09..10.47 rows=51 width=37) (actual time=16.694..16.872 rows=48 loops=1)
-> Sort (cost=10.09..10.22 rows=51 width=61) (actual time=16.685..16.724 rows=48 loops=1)
Sort Key: programs.id_publisher, programs.id_program
-> Seq Scan on programs (cost=0.00..8.65 rows=51 width=61) (actual time=3.128..16.498 rows=48 loops=1)
Filter: (id_program <> 0)
-> Hash (cost=3.04..3.04 rows=1 width=8) (actual time=0.076..0.076 rows=0 loops=1)
-> Index Scan using idx_id_url_sat_request on sat_request sr (cost=0.00..3.04 rows=1 width=8) (actual time=0.072..0.072 rows=0 loops=1)
Index Cond: (id_url = 329268)
Filter: ((id_user = 29416) AND (id_sat_request_status = 1))
Total runtime: 1253.831 ms
(35 rows)

empdb=# set enable_hashjoin = false;
SET
empdb=# explain analyze SELECT id_sat_request
empdb-# FROM sat_request sr,
empdb-# v_sc_packages vs
empdb-# WHERE sr.id_package = vs.id_package AND
empdb-# id_user = 29416 AND
empdb-# id_url = 329268 AND
empdb-# vs.estimated_start > now() AND
empdb-# id_sat_request_status = sp_lookup_id('sat_request_status', 'Scheduled');
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=425.91..436.70 rows=1 width=4) (actual time=0.024..0.024 rows=0 loops=1)
Join Filter: ("outer".id_package = "inner".id_package)
-> Index Scan using idx_id_url_sat_request on sat_request sr (cost=0.00..3.04 rows=1 width=8) (actual time=0.021..0.021 rows=0 loops=1)
Index Cond: (id_url = 329268)
Filter: ((id_user = 29416) AND (id_sat_request_status = 1))
-> Subquery Scan vs (cost=425.91..429.79 rows=517 width=4) (never executed)
-> Sort (cost=425.91..427.20 rows=517 width=263) (never executed)
Sort Key: vs.estimated_start
-> Merge Join (cost=391.42..402.61 rows=517 width=263) (never executed)
Merge Cond: ("outer".id_package = "inner".id_package)
-> Sort (cost=103.96..104.19 rows=94 width=49) (never executed)
Sort Key: vs.id_package
-> Merge Join (cost=96.28..100.88 rows=94 width=49) (never executed)
Merge Cond: ("outer".id_program = "inner".id_program)
-> Subquery Scan vs (cost=84.36..87.10 rows=366 width=16) (never executed)
-> Sort (cost=84.36..85.27 rows=366 width=20) (never executed)
Sort Key: sequences.id_program, sequences.internal_position
-> Seq Scan on sequences (cost=0.00..68.78 rows=366 width=20) (never executed)
Filter: ((estimated_start IS NOT NULL) AND (date_trunc('seconds'::text, estimated_start) > now()))
-> Sort (cost=11.92..12.05 rows=51 width=37) (never executed)
Sort Key: vpr.id_program
-> Subquery Scan vpr (cost=10.09..10.47 rows=51 width=37) (never executed)
-> Sort (cost=10.09..10.22 rows=51 width=61) (never executed)
Sort Key: programs.id_publisher, programs.id_program
-> Seq Scan on programs (cost=0.00..8.65 rows=51 width=61) (never executed)
Filter: (id_program <> 0)
-> Sort (cost=287.46..290.23 rows=1106 width=218) (never executed)
Sort Key: vpk.id_package
-> Subquery Scan vpk (cost=223.25..231.55 rows=1106 width=218) (never executed)
-> Sort (cost=223.25..226.02 rows=1106 width=162) (never executed)
Sort Key: p.id_publisher, p.name
-> Merge Right Join (cost=109.44..167.34 rows=1106 width=162) (never executed)
Merge Cond: ("outer".id_package = "inner".id_package)
-> Index Scan using package_security_id_package_key on package_security ps (cost=0.00..38.70 rows=727 width=20) (never executed)
-> Sort (cost=109.44..112.21 rows=1106 width=146) (never executed)
Sort Key: p.id_package
-> Seq Scan on packages p (cost=0.00..53.53 rows=1106 width=146) (never executed)
Total runtime: 0.396 ms
(38 rows)

empdb=# set enable_mergejoin = false;
SET
empdb=# explain analyze SELECT id_sat_request
empdb-# FROM sat_request sr,
empdb-# v_sc_packages vs
empdb-# WHERE sr.id_package = vs.id_package AND
empdb-# id_user = 29416 AND
empdb-# id_url = 329268 AND
empdb-# vs.estimated_start > now() AND
empdb-# id_sat_request_status = sp_lookup_id('sat_request_status', 'Scheduled');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=5987.81..5998.60 rows=1 width=4) (actual time=0.129..0.129 rows=0 loops=1)
Join Filter: ("outer".id_package = "inner".id_package)
-> Index Scan using idx_id_url_sat_request on sat_request sr (cost=0.00..3.04 rows=1 width=8) (actual time=0.127..0.127 rows=0 loops=1)
Index Cond: (id_url = 329268)
Filter: ((id_user = 29416) AND (id_sat_request_status = 1))
-> Subquery Scan vs (cost=5987.81..5991.68 rows=517 width=4) (never executed)
-> Sort (cost=5987.81..5989.10 rows=517 width=263) (never executed)
Sort Key: vs.estimated_start
-> Nested Loop (cost=4426.30..5964.51 rows=517 width=263) (never executed)
Join Filter: ("inner".id_package = "outer".id_package)
-> Nested Loop (cost=94.83..330.90 rows=94 width=49) (never executed)
Join Filter: ("inner".id_program = "outer".id_program)
-> Subquery Scan vs (cost=84.36..87.10 rows=366 width=16) (never executed)
-> Sort (cost=84.36..85.27 rows=366 width=20) (never executed)
Sort Key: sequences.id_program, sequences.internal_position
-> Seq Scan on sequences (cost=0.00..68.78 rows=366 width=20) (never executed)
Filter: ((estimated_start IS NOT NULL) AND (date_trunc('seconds'::text, estimated_start) > now()))
-> Materialize (cost=10.47..10.73 rows=51 width=37) (never executed)
-> Subquery Scan vpr (cost=10.09..10.47 rows=51 width=37) (never executed)
-> Sort (cost=10.09..10.22 rows=51 width=61) (never executed)
Sort Key: programs.id_publisher, programs.id_program
-> Seq Scan on programs (cost=0.00..8.65 rows=51 width=61) (never executed)
Filter: (id_program <> 0)
-> Materialize (cost=4331.47..4337.00 rows=1106 width=218) (never executed)
-> Subquery Scan vpk (cost=4323.17..4331.47 rows=1106 width=218) (never executed)
-> Sort (cost=4323.17..4325.94 rows=1106 width=162) (never executed)
Sort Key: p.id_publisher, p.name
-> Nested Loop Left Join (cost=0.00..4267.26 rows=1106 width=162) (never executed)
-> Seq Scan on packages p (cost=0.00..53.53 rows=1106 width=146) (never executed)
-> Index Scan using package_security_id_package_key on package_security ps (cost=0.00..3.79 rows=1 width=20) (never executed)
Index Cond: ("outer".id_package = ps.id_package)
Total runtime: 0.351 ms
(32 rows)

empdb=# set enable_hashjoin = true;
SET
empdb=# explain analyze SELECT id_sat_request
empdb-# FROM sat_request sr,
empdb-# v_sc_packages vs
empdb-# WHERE sr.id_package = vs.id_package AND
empdb-# id_user = 29416 AND
empdb-# id_url = 329268 AND
empdb-# vs.estimated_start > now() AND
empdb-# id_sat_request_status = sp_lookup_id('sat_request_status', 'Scheduled');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=320.18..326.65 rows=1 width=4) (actual time=334.756..334.756 rows=0 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Subquery Scan vs (cost=317.13..321.01 rows=517 width=4) (actual time=332.372..333.869 rows=663 loops=1)
-> Sort (cost=317.13..318.43 rows=517 width=263) (actual time=332.366..332.792 rows=663 loops=1)
Sort Key: vs.estimated_start
-> Hash Join (cost=249.94..293.83 rows=517 width=263) (actual time=314.677..328.567 rows=663 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Subquery Scan vpk (cost=145.12..153.42 rows=1106 width=218) (actual time=295.296..302.578 rows=1118 loops=1)
-> Sort (cost=145.12..147.89 rows=1106 width=162) (actual time=295.282..296.039 rows=1118 loops=1)
Sort Key: p.id_publisher, p.name
-> Hash Left Join (cost=15.45..89.21 rows=1106 width=162) (actual time=2.795..283.805 rows=1118 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Seq Scan on packages p (cost=0.00..53.53 rows=1106 width=146) (actual time=0.006..2.049 rows=1118 loops=1)
-> Hash (cost=13.63..13.63 rows=727 width=20) (actual time=2.043..2.043 rows=0 loops=1)
-> Seq Scan on package_security ps (cost=0.00..13.63 rows=727 width=20) (actual time=0.013..1.235 rows=756 loops=1)
-> Hash (cost=104.58..104.58 rows=94 width=49) (actual time=19.237..19.237 rows=0 loops=1)
-> Hash Join (cost=94.96..104.58 rows=94 width=49) (actual time=15.295..18.526 rows=663 loops=1)
Hash Cond: ("outer".id_program = "inner".id_program)
-> Subquery Scan vs (cost=84.36..87.10 rows=366 width=16) (actual time=5.045..6.632 rows=663 loops=1)
-> Sort (cost=84.36..85.27 rows=366 width=20) (actual time=5.037..5.466 rows=663 loops=1)
Sort Key: sequences.id_program, sequences.internal_position
-> Seq Scan on sequences (cost=0.00..68.78 rows=366 width=20) (actual time=0.301..4.065 rows=663 loops=1)
Filter: ((estimated_start IS NOT NULL) AND (date_trunc('seconds'::text, estimated_start) > now()))
-> Hash (cost=10.47..10.47 rows=51 width=37) (actual time=10.027..10.027 rows=0 loops=1)
-> Subquery Scan vpr (cost=10.09..10.47 rows=51 width=37) (actual time=9.839..9.954 rows=48 loops=1)
-> Sort (cost=10.09..10.22 rows=51 width=61) (actual time=9.834..9.864 rows=48 loops=1)
Sort Key: programs.id_publisher, programs.id_program
-> Seq Scan on programs (cost=0.00..8.65 rows=51 width=61) (actual time=0.603..9.730 rows=48 loops=1)
Filter: (id_program <> 0)
-> Hash (cost=3.04..3.04 rows=1 width=8) (actual time=0.030..0.030 rows=0 loops=1)
-> Index Scan using idx_id_url_sat_request on sat_request sr (cost=0.00..3.04 rows=1 width=8) (actual time=0.028..0.028 rows=0 loops=1)
Index Cond: (id_url = 329268)
Filter: ((id_user = 29416) AND (id_sat_request_status = 1))
Total runtime: 335.600 ms
(34 rows)




For the 2.

empdb=# explain analyze SELECT id_sat_request
empdb-# FROM sat_request sr,
empdb-# v_sc_packages vs
empdb-# WHERE sr.id_package = vs.id_package AND
empdb-# sr.id_user = 24927 AND
empdb-# sr.id_url = 28 AND
empdb-# vs.estimated_start > now() AND
empdb-# sr.id_sat_request_status = sp_lookup_id('sat_request_status', 'Scheduled');
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=286.76..291.22 rows=1 width=4) (actual time=350.213..350.213 rows=0 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Subquery Scan vs (cost=283.66..286.33 rows=356 width=4) (actual time=348.046..349.462 rows=664 loops=1)
-> Sort (cost=283.66..284.55 rows=356 width=263) (actual time=348.040..348.468 rows=664 loops=1)
Sort Key: vs.estimated_start
-> Hash Join (cost=234.58..268.57 rows=356 width=263) (actual time=329.691..344.072 rows=664 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Subquery Scan vpk (cost=145.12..153.42 rows=1106 width=218) (actual time=310.687..318.244 rows=1117 loops=1)
-> Sort (cost=145.12..147.89 rows=1106 width=162) (actual time=310.671..311.402 rows=1117 loops=1)
Sort Key: p.id_publisher, p.name
-> Hash Left Join (cost=15.45..89.21 rows=1106 width=162) (actual time=2.735..299.686 rows=1117 loops=1)
Hash Cond: ("outer".id_package = "inner".id_package)
-> Seq Scan on packages p (cost=0.00..53.53 rows=1106 width=146) (actual time=0.006..2.132 rows=1117 loops=1)
-> Hash (cost=13.63..13.63 rows=727 width=20) (actual time=1.991..1.991 rows=0 loops=1)
-> Seq Scan on package_security ps (cost=0.00..13.63 rows=727 width=20) (actual time=0.014..1.222 rows=755 loops=1)
-> Hash (cost=89.29..89.29 rows=65 width=49) (actual time=18.856..18.856 rows=0 loops=1)
-> Merge Join (cost=86.12..89.29 rows=65 width=49) (actual time=13.900..18.150 rows=664 loops=1)
Merge Cond: ("outer".id_program = "inner".id_program)
-> Subquery Scan vs (cost=74.20..76.09 rows=252 width=16) (actual time=4.935..6.539 rows=664 loops=1)
-> Sort (cost=74.20..74.83 rows=252 width=20) (actual time=4.925..5.369 rows=664 loops=1)
Sort Key: sequences.id_program, sequences.internal_position
-> Seq Scan on sequences (cost=0.00..64.15 rows=252 width=20) (actual time=0.304..3.876 rows=664 loops=1)
Filter: ((estimated_start IS NOT NULL) AND (date_trunc('seconds'::text, estimated_start) > now()))
-> Sort (cost=11.92..12.05 rows=51 width=37) (actual time=8.950..9.403 rows=699 loops=1)
Sort Key: vpr.id_program
-> Subquery Scan vpr (cost=10.09..10.47 rows=51 width=37) (actual time=8.743..8.861 rows=48 loops=1)
-> Sort (cost=10.09..10.22 rows=51 width=61) (actual time=8.738..8.768 rows=48 loops=1)
Sort Key: programs.id_publisher, programs.id_program
-> Seq Scan on programs (cost=0.00..8.65 rows=51 width=61) (actual time=0.532..8.650 rows=48 loops=1)
Filter: (id_program <> 0)
-> Hash (cost=3.10..3.10 rows=1 width=8) (actual time=0.090..0.090 rows=0 loops=1)
-> Index Scan using idx_id_url_sat_request on sat_request sr (cost=0.00..3.10 rows=1 width=8) (actual time=0.045..0.052 rows=2 loops=1)
Index Cond: (id_url = 28)
Filter: ((id_user = 24927) AND (id_sat_request_status = 1))
Total runtime: 351.070 ms
(35 rows)


empdb=# set enable_hashjoin = false;
SET
empdb=# explain analyze SELECT id_sat_request
empdb-# FROM sat_request sr,
empdb-# v_sc_packages vs
empdb-# WHERE sr.id_package = vs.id_package AND
empdb-# sr.id_user = 24927 AND
empdb-# sr.id_url = 28 AND
empdb-# vs.estimated_start > now() AND
empdb-# sr.id_sat_request_status = sp_lookup_id('sat_request_status', 'Scheduled');
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=403.23..411.68 rows=1 width=4) (actual time=591.062..591.062 rows=0 loops=1)
Join Filter: ("outer".id_package = "inner".id_package)
-> Index Scan using idx_id_url_sat_request on sat_request sr (cost=0.00..3.10 rows=1 width=8) (actual time=0.142..0.166 rows=2 loops=1)
Index Cond: (id_url = 28)
Filter: ((id_user = 24927) AND (id_sat_request_status = 1))
-> Subquery Scan vs (cost=403.23..405.90 rows=356 width=4) (actual time=293.456..294.865 rows=663 loops=2)
-> Sort (cost=403.23..404.12 rows=356 width=263) (actual time=293.452..293.850 rows=663 loops=2)
Sort Key: vs.estimated_start
-> Merge Join (cost=378.71..388.15 rows=356 width=263) (actual time=573.592..583.547 rows=663 loops=1)
Merge Cond: ("outer".id_package = "inner".id_package)
-> Sort (cost=91.25..91.41 rows=65 width=49) (actual time=31.404..31.839 rows=663 loops=1)
Sort Key: vs.id_package
-> Merge Join (cost=86.12..89.29 rows=65 width=49) (actual time=23.455..30.155 rows=663 loops=1)
Merge Cond: ("outer".id_program = "inner".id_program)
-> Subquery Scan vs (cost=74.20..76.09 rows=252 width=16) (actual time=9.143..11.583 rows=663 loops=1)
-> Sort (cost=74.20..74.83 rows=252 width=20) (actual time=9.130..9.695 rows=663 loops=1)
Sort Key: sequences.id_program, sequences.internal_position
-> Seq Scan on sequences (cost=0.00..64.15 rows=252 width=20) (actual time=0.646..7.404 rows=663 loops=1)
Filter: ((estimated_start IS NOT NULL) AND (date_trunc('seconds'::text, estimated_start) > now()))
-> Sort (cost=11.92..12.05 rows=51 width=37) (actual time=14.297..14.911 rows=699 loops=1)
Sort Key: vpr.id_program
-> Subquery Scan vpr (cost=10.09..10.47 rows=51 width=37) (actual time=14.041..14.208 rows=48 loops=1)
-> Sort (cost=10.09..10.22 rows=51 width=61) (actual time=14.033..14.075 rows=48 loops=1)
Sort Key: programs.id_publisher, programs.id_program
-> Seq Scan on programs (cost=0.00..8.65 rows=51 width=61) (actual time=0.590..13.871 rows=48 loops=1)
Filter: (id_program <> 0)
-> Sort (cost=287.46..290.23 rows=1106 width=218) (actual time=542.090..542.856 rows=1121 loops=1)
Sort Key: vpk.id_package
-> Subquery Scan vpk (cost=223.25..231.55 rows=1106 width=218) (actual time=533.719..540.405 rows=1118 loops=1)
-> Sort (cost=223.25..226.02 rows=1106 width=162) (actual time=533.701..534.387 rows=1118 loops=1)
Sort Key: p.id_publisher, p.name
-> Merge Right Join (cost=109.44..167.34 rows=1106 width=162) (actual time=5.971..521.177 rows=1118 loops=1)
Merge Cond: ("outer".id_package = "inner".id_package)
-> Index Scan using package_security_id_package_key on package_security ps (cost=0.00..38.70 rows=727 width=20) (actual time=0.042..6.572 rows=755 loops=1)
-> Sort (cost=109.44..112.21 rows=1106 width=146) (actual time=5.132..6.274 rows=1118 loops=1)
Sort Key: p.id_package
-> Seq Scan on packages p (cost=0.00..53.53 rows=1106 width=146) (actual time=0.008..2.016 rows=1118 loops=1)
Total runtime: 592.818 ms
(38 rows)


empdb=# set enable_mergejoin = false;
SET
empdb=# explain analyze SELECT id_sat_request
empdb-# FROM sat_request sr,
empdb-# v_sc_packages vs
empdb-# WHERE sr.id_package = vs.id_package AND
empdb-# sr.id_user = 24927 AND
empdb-# sr.id_url = 28 AND
empdb-# vs.estimated_start > now() AND
empdb-# sr.id_sat_request_status = sp_lookup_id('sat_request_status', 'Scheduled');
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=5494.18..5502.62 rows=1 width=4) (actual time=1452.808..1452.808 rows=0 loops=1)
Join Filter: ("outer".id_package = "inner".id_package)
-> Index Scan using idx_id_url_sat_request on sat_request sr (cost=0.00..3.10 rows=1 width=8) (actual time=0.097..0.129 rows=2 loops=1)
Index Cond: (id_url = 28)
Filter: ((id_user = 24927) AND (id_sat_request_status = 1))
-> Subquery Scan vs (cost=5494.18..5496.85 rows=356 width=4) (actual time=723.953..725.618 rows=476 loops=2)
-> Sort (cost=5494.18..5495.07 rows=356 width=263) (actual time=723.947..724.401 rows=476 loops=2)
Sort Key: vs.estimated_start
-> Nested Loop (cost=4416.14..5479.09 rows=356 width=263) (actual time=391.941..1444.468 rows=476 loops=1)
Join Filter: ("inner".id_package = "outer".id_package)
-> Nested Loop (cost=84.68..247.22 rows=65 width=49) (actual time=12.618..61.719 rows=476 loops=1)
Join Filter: ("inner".id_program = "outer".id_program)
-> Subquery Scan vs (cost=74.20..76.09 rows=252 width=16) (actual time=4.022..7.144 rows=476 loops=1)
-> Sort (cost=74.20..74.83 rows=252 width=20) (actual time=4.015..4.670 rows=476 loops=1)
Sort Key: sequences.id_program, sequences.internal_position
-> Seq Scan on sequences (cost=0.00..64.15 rows=252 width=20) (actual time=0.415..3.319 rows=476 loops=1)
Filter: ((estimated_start IS NOT NULL) AND (date_trunc('seconds'::text, estimated_start) > now()))
-> Materialize (cost=10.47..10.73 rows=51 width=37) (actual time=0.019..0.054 rows=48 loops=476)
-> Subquery Scan vpr (cost=10.09..10.47 rows=51 width=37) (actual time=8.580..8.714 rows=48 loops=1)
-> Sort (cost=10.09..10.22 rows=51 width=61) (actual time=8.575..8.609 rows=48 loops=1)
Sort Key: programs.id_publisher, programs.id_program
-> Seq Scan on programs (cost=0.00..8.65 rows=51 width=61) (actual time=0.396..8.481 rows=48 loops=1)
Filter: (id_program <> 0)
-> Materialize (cost=4331.47..4337.00 rows=1106 width=218) (actual time=0.797..1.635 rows=1118 loops=476)
-> Subquery Scan vpk (cost=4323.17..4331.47 rows=1106 width=218) (actual time=379.131..385.923 rows=1118 loops=1)
-> Sort (cost=4323.17..4325.94 rows=1106 width=162) (actual time=379.114..379.855 rows=1118 loops=1)
Sort Key: p.id_publisher, p.name
-> Nested Loop Left Join (cost=0.00..4267.26 rows=1106 width=162) (actual time=0.512..367.899 rows=1118 loops=1)
-> Seq Scan on packages p (cost=0.00..53.53 rows=1106 width=146) (actual time=0.008..2.844 rows=1118 loops=1)
-> Index Scan using package_security_id_package_key on package_security ps (cost=0.00..3.79 rows=1 width=20) (actual time=0.011..0.013 rows=1 loops=1118)
Index Cond: ("outer".id_package = ps.id_package)
Total runtime: 1454.219 ms
(32 rows)





Regards
Gaetano Mendola