|
Dear pgadmin-general, pgadmin-performance, I have the following query:
What could be due to a single query sql one day run fast and another slow. The
database has massive loads overnight and the next day serves datawarehouse.
This causes the first query is slower by having to go find all the information
to disk. With a new process is achieved load data from disk cache operating
system that is able to improve response times. I have doubts as to refresh
cache and as the LRU (least recently used) works. One way to optimize the query
was generating "Common Table Expressions", here is my other question,
where are CTE's stored?, Work_mem?, Buffer cache?, Temporary tablespace ?, etc
.. Deputy some parameter values postgresql : max_connections = 40 checkpoint_completion_target = 0.9 random_page_cost=20 seq_page_cost = 20 default_statistics_target = 100 # pgtune
wizard 2014-11-20 maintenance_work_mem = 1GB # pgtune wizard
2014-11-20 constraint_exclusion = on # pgtune wizard
2014-11-20 effective_cache_size = 30GB # pgtune wizard
2014-11-20 work_mem = 800MB # pgtune wizard 2014-11-20 wal_buffers = 32MB # pgtune wizard
2014-11-20 checkpoint_segments = 64 # pgtune wizard
2014-11-20 shared_buffers = 5GB # pgtune wizard
2014-11-20 Summary charging procedure cache operating
system: select distinct relfilenode from
pg_buffercache > /tmp/cacheprecarga.lst cat /tmp/cacheprecarga.lst | while read
line; do find /var/lib/pgsql/9.2/data/base/ -name $line -print -exec cat '{}'
> /dev/null \; ; done The query in question
is: With CTE’s (Common
table expressions) : WITH PPTEMP AS
(SELECT * FROM PROPRO WHERE PV_KEY = 3268), IDTEMP AS (SELECT * FROM
INVDET WHERE ID_INT9 IN (1)), CPTEMP AS (select * from catprod where
CP_KEYN1 = 28 and PV_KEY = 1) SELECT PR.PR_KEY AS PRKEY,PR.PR_CODIGO1 AS
CODRETAILER,PP.PP_CODPROV AS CODPROVIDER,PR.PR_DESC_LARGA AS
PRODUCTDESCRIPTION,SUM(ID.ID_DEC14) AS LAST28DAYSSALESMC,CASE WHEN
SUM(ID.ID_MSTOCK) != 0 THEN (((SUM(ID.ID_DEC20)/1.19) - SUM(ID.ID_MSTOCK))
/SUM(ID.ID_MSTOCK)) *100 ELSE 0 END AS MARGIN,SUM(ID.ID_MSTOCK) AS STOCKMC,CASE
WHEN SUM(ID.ID_DEC14) != 0 THEN (SUM(ID.ID_MSTOCK)/SUM(ID.ID_DEC14))*28 ELSE 0
END AS DAYSALES FROM PPTEMP PP JOIN PRODUCTO PR ON PP.PR_KEY = PR.PR_KEY JOIN
IDTEMP ID ON PP.PR_KEY = ID.PR_KEY JOIN LOCAL LO ON ID.LO_KEY = LO.LO_KEY JOIN
LOCPROV LP ON LO.LO_KEY = LP.LO_KEY, CATPROD CP WHERE LP.PV_KEY =
PP.PV_KEY AND CP.CP_KEY = PR.CP_KEY GROUP BY
PR.PR_KEY,PR.PR_CODIGO1,PP.PP_CODPROV,PR.PR_DESC_LARGA ORDER BY
LAST28DAYSSALESMC DESC FETCH FIRST 20 ROWS ONLY; prkey |
codretailer | codprovider
| productdescription
| last28dayssalesmc |
margin |
stockmc | daysales --------+-------------+---------------+------------------------------------------+-------------------+--------------------------+--------------+--------------------- 272309 |
578525 | 1000000232615 | DET POLVO OMO MATIC
MULTIACCION 3.8KG |
208949598.00 | -94.78519887547606157500 | 642572117.00 | 86.1069820100826420 . . Time: 142200.432 ms Result : 2.30 minutes Other query : WITH PRTEMP AS
(SELECT * FROM PRODUCTO WHERE PR_ESTADO = 1 ), PPTEMP AS (SELECT * FROM
PROPRO WHERE PV_KEY = 3268) SELECT COALESCE(SUM(VT.VM_CANTIDAD),0) AS
UNITS, COALESCE(SUM(VT.VM_MTO_PC),0) AS MONTOPC, COALESCE(SUM(VT.VM_MTO_PV),0)
AS MONTOPV FROM PPTEMP PP ,PRTEMP PR ,VENTAMC VT ,LOCAL LO ,LOCPROV
LP WHERE PP.PR_KEY = PR.PR_KEY AND PP.PR_KEY = VT.PR_KEY AND
VT.LO_KEY = LO.LO_KEY AND LO.LO_KEY = LP.LO_KEY AND LP.PV_KEY = PP.PV_KEY AND
VT.PE_KEY >= 72 AND VT.PE_KEY <= 82; Timing is on.
units | montopc
| montopv -------------+----------------+---------------- 42592548.00 |
48920814088.00 | 50700171188.00 (1 row) Time: 535717.964 ms 9 minutes aprox. With temporary
tables : CREATE TEMP TABLE
PPTEMP AS SELECT * FROM PROPRO WHERE PV_KEY = 3268; CREATE TEMP TABLE
IDTEMP AS SELECT * FROM INVDET WHERE ID_INT9 IN (1); CREATE TEMP TABLE
CPTEMP AS select * from catprod where CP_KEYN1 = 28 and PV_KEY = 1; SELECT PR.PR_KEY AS PRKEY,PR.PR_CODIGO1 AS
CODRETAILER,PP.PP_CODPROV AS CODPROVIDER,PR.PR_DESC_LARGA AS
PRODUCTDESCRIPTION,SUM(ID.ID_DEC14) AS LAST28DAYSSALESMC,CASE WHEN
SUM(ID.ID_MSTOCK) != 0 THEN (((SUM(ID.ID_DEC20)/1.19) - SUM(ID.ID_MSTOCK))
/SUM(ID.ID_MSTOCK)) *100 ELSE 0 END AS MARGIN,SUM(ID.ID_MSTOCK) AS STOCKMC,CASE
WHEN SUM(ID.ID_DEC14) != 0 THEN (SUM(ID.ID_MSTOCK)/SUM(ID.ID_DEC14))*28 ELSE 0
END AS DAYSALES FROM PPTEMP PP JOIN PRODUCTO PR ON PP.PR_KEY = PR.PR_KEY JOIN
IDTEMP ID ON PP.PR_KEY = ID.PR_KEY JOIN LOCAL LO ON ID.LO_KEY = LO.LO_KEY JOIN
LOCPROV LP ON LO.LO_KEY = LP.LO_KEY, CPTEMP CP WHERE LP.PV_KEY =
PP.PV_KEY AND CP.CP_KEY = PR.CP_KEY GROUP BY
PR.PR_KEY,PR.PR_CODIGO1,PP.PP_CODPROV,PR.PR_DESC_LARGA ORDER BY
LAST28DAYSSALESMC DESC FETCH FIRST 20 ROWS ONLY; Result : Timing is on. SELECT 3653 Time: 2147.027 ms SELECT 4216057 Time: 134861.333 ms SELECT 433 Time: 1474.354 ms prkey |
codretailer | codprovider
|
productdescription
| last28dayssalesmc |
margin |
stockmc | daysales --------+-------------+-------------+------------------------------------------+-------------------+--------------------------+--------------+--------------------- 3781 |
10579 |
23935 | TE CEYLAN EMBLEM 100
UN
| 82933398.00 | -75.90128158302451567400 |
230014798.00 | 77.6576686752905024 . . (20 rows) Time: 43738.024 ms Total : 3 minutes aprox. Other query : CREATE TEMP TABLE
PRTEMP AS SELECT * FROM PRODUCTO WHERE PR_ESTADO = 1; CREATE TEMP TABLE
PPTEMP AS SELECT * FROM PROPRO WHERE PV_KEY = 3268; SELECT
COALESCE(SUM(VT.VM_CANTIDAD),0) AS UNITS, COALESCE(SUM(VT.VM_MTO_PC),0) AS
MONTOPC, COALESCE(SUM(VT.VM_MTO_PV),0) AS MONTOPV FROM PPTEMP PP ,PRTEMP PR
,VENTAMC VT ,LOCAL LO ,LOCPROV LP WHERE PP.PR_KEY = PR.PR_KEY
AND PP.PR_KEY = VT.PR_KEY AND VT.LO_KEY = LO.LO_KEY AND LO.LO_KEY = LP.LO_KEY
AND LP.PV_KEY = PP.PV_KEY AND VT.PE_KEY >= 72 AND VT.PE_KEY <= 82; Timing is on. SELECT 224119 Time: 782.938 ms SELECT 3653 Time: 1974.144 ms
units | montopc
| montopv -------------+----------------+---------------- 42592548.00 |
48920814088.00 | 50700171188.00 (1 row) Time: 524025.660 ms 9 minutes aprox. ----------------------------------------------------------------------------------------------------------- The larger tables are
: INVDET : 8565114 VENTAMC : 57714192 Execution plan : dbcom=# CREATE TEMP
TABLE PPTEMP AS SELECT * FROM PROPRO WHERE PV_KEY = 3268; SELECT 3653 dbcom=# CREATE TEMP
TABLE IDTEMP AS SELECT * FROM INVDET WHERE ID_INT9 IN (1); SELECT 4216057 dbcom=# CREATE TEMP
TABLE CPTEMP AS select * from catprod where CP_KEYN1 = 28 and PV_KEY = 1; SELECT 433 dbcom=# explain
SELECT PR.PR_KEY AS PRKEY,PR.PR_CODIGO1 AS CODRETAILER,PP.PP_CODPROV AS
CODPROVIDER,PR.PR_DESC_LARGA AS PRODUCTDESCRIPTION,SUM(ID.ID_DEC14) AS
LAST28DAYSSALESMC,CASE WHEN SUM(ID.ID_MSTOCK) != 0 THEN (((SUM(ID.ID_DEC20)/1.19)
- SUM(ID.ID_MSTOCK)) /SUM(ID.ID_MSTOCK)) *100 ELSE 0 END AS
MARGIN,SUM(ID.ID_MSTOCK) AS STOCKMC,CASE WHEN SUM(ID.ID_DEC14) != 0 THEN
(SUM(ID.ID_MSTOCK)/SUM(ID.ID_DEC14))*28 ELSE 0 END AS DAYSALES FROM PPTEMP PP
JOIN PRODUCTO PR ON PP.PR_KEY = PR.PR_KEY JOIN IDTEMP ID ON PP.PR_KEY =
ID.PR_KEY JOIN LOCAL LO ON ID.LO_KEY = LO.LO_KEY JOIN LOCPROV LP ON LO.LO_KEY =
LP.LO_KEY, CPTEMP CP WHERE LP.PV_KEY = PP.PV_KEY AND CP.CP_KEY =
PR.CP_KEY GROUP BY PR.PR_KEY,PR.PR_CODIGO1,PP.PP_CODPROV,PR.PR_DESC_LARGA
ORDER BY LAST28DAYSSALESMC DESC FETCH FIRST 20 ROWS ONLY;
QUERY PLAN --------------------------------------------------------------------------------------------------------------------------------------- Limit
(cost=2000131.06..2000131.11 rows=20 width=238)
-> Sort (cost=2000131.06..2000803.75 rows=269077 width=238)
Sort Key: (sum(id.id_dec14))
-> HashAggregate (cost=1984898.71..1992971.02 rows=269077
width=238)
-> Hash Join (cost=75057.66..1976153.71 rows=269077 width=238)
Hash Cond: ((id.pr_key = pp.pr_key) AND (id.lo_key = lo.lo_key))
-> Seq Scan on idtemp id (cost=0.00..1757879.62 rows=1221962
width=72)
-> Hash (cost=74505.39..74505.39 rows=36818 width=206)
-> Hash Join (cost=620.25..74505.39 rows=36818 width=206)
Hash Cond: (lp.lo_key = lo.lo_key)
->
Nested Loop (cost=321.44..73700.33 rows=36818 width=198)
-> Hash Join (cost=321.44..35652.72 rows=44 width=198)
Hash Cond: (pr.cp_key = cp.cp_key)
-> Nested Loop (cost=0.00..35326.25 rows=1224 width=206)
-> Seq Scan on pptemp pp (cost=0.00..732.24 rows=1224 width=154)
-> Index Scan using producto_pk on producto pr (cost=0.00..28.25
rows=1 width=52)
Index Cond: (pr_key = pp.pr_key)
->
Hash (cost=320.64..320.64 rows=64 width=8)
-> Seq Scan on cptemp cp (cost=0.00..320.64 rows=64 width=8)
-> Index Scan using locprov_pv_key_idx on locprov lp
(cost=0.00..856.34 rows=838 width=16)
Index Cond: (pv_key = pp.pv_key)
-> Hash (cost=288.36..288.36 rows=836 width=8)
-> Seq Scan on local lo (cost=0.00..288.36 rows=836 width=8) (23 rows) WITH PPTEMP AS
(SELECT * FROM PROPRO WHERE PV_KEY = 3268), IDTEMP AS (SELECT * FROM
INVDET WHERE ID_INT9 IN (1)), CPTEMP AS (select * from catprod where
CP_KEYN1 = 28 and PV_KEY = 1) SELECT PR.PR_KEY AS PRKEY,PR.PR_CODIGO1 AS
CODRETAILER,PP.PP_CODPROV AS CODPROVIDER,PR.PR_DESC_LARGA AS
PRODUCTDESCRIPTION,SUM(ID.ID_DEC14) AS LAST28DAYSSALESMC,CASE WHEN
SUM(ID.ID_MSTOCK) != 0 THEN (((SUM(ID.ID_DEC20)/1.19) - SUM(ID.ID_MSTOCK))
/SUM(ID.ID_MSTOCK)) *100 ELSE 0 END AS MARGIN,SUM(ID.ID_MSTOCK) AS STOCKMC,CASE
WHEN SUM(ID.ID_DEC14) != 0 THEN (SUM(ID.ID_MSTOCK)/SUM(ID.ID_DEC14))*28 ELSE 0
END AS DAYSALES FROM PPTEMP PP JOIN PRODUCTO PR ON PP.PR_KEY = PR.PR_KEY JOIN
IDTEMP ID ON PP.PR_KEY = ID.PR_KEY JOIN LOCAL LO ON ID.LO_KEY = LO.LO_KEY JOIN
LOCPROV LP ON LO.LO_KEY = LP.LO_KEY, CATPROD CP WHERE LP.PV_KEY =
PP.PV_KEY AND CP.CP_KEY = PR.CP_KEY GROUP BY
PR.PR_KEY,PR.PR_CODIGO1,PP.PP_CODPROV,PR.PR_DESC_LARGA ORDER BY
LAST28DAYSSALESMC DESC FETCH FIRST 20 ROWS ONLY"
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Limit
(cost=1423557229.97..1423557230.02 rows=20 width=238) CTE
pptemp
-> Index Scan using propro_pv_key_idx on propro
(cost=0.00..46293.87 rows=3407 width=46)
Index Cond: (pv_key = 3268) CTE
idtemp
-> Index Scan using invdet_id_int9_idx on invdet (cost=0.00..3211054.04
rows=4165931 width=126)
Index Cond: (id_int9 = 1)
-> Sort (cost=1420299882.07..1420411941.57 rows=44823800
width=238)
Sort Key: (sum(id.id_dec14))
-> GroupAggregate (cost=1384421302.46..1419107136.86 rows=44823800
width=238)
-> Sort (cost=1384421302.46..1386802811.06 rows=952603440
width=238)
Sort Key: pr.pr_key, pr.pr_codigo1, pp.pp_codprov, pr.pr_desc_larga
-> Hash Join (cost=2261805.19..14387957.07 rows=952603440
width=238)
Hash Cond: (pp.pr_key = pr.pr_key)
-> Merge Join (cost=2025051.21..3291356.72 rows=71136713
width=210)
Merge Cond: ((pp.pr_key = id.pr_key) AND (lo.lo_key = id.lo_key))
-> Sort (cost=1483684.16..1490821.85 rows=2855078 width=162)
Sort Key: pp.pr_key, lp.lo_key
-> Hash Join (cost=1096170.53..1177547.01 rows=2855078
width=162)
Hash Cond: (lp.lo_key = lo.lo_key)
-> Hash Join (cost=1095871.72..1137990.88 rows=2855078
width=154)
Hash Cond: (pp.pv_key = lp.pv_key)
-> CTE Scan on pptemp pp (cost=0.00..68.14 rows=3407 width=154)
->
Hash (cost=1004487.43..1004487.43 rows=7310743 width=16)
-> Seq Scan on locprov lp (cost=0.00..1004487.43 rows=7310743
width=16)
->
Hash (cost=288.36..288.36 rows=836 width=8)
-> Seq Scan on local lo (cost=0.00..288.36 rows=836 width=8)
-> Sort (cost=541367.06..551781.88 rows=4165931 width=72)
Sort Key: id.pr_key, id.lo_key
-> CTE Scan on idtemp id (cost=0.00..83318.62 rows=4165931
width=72)
-> Hash (cost=199238.80..199238.80 rows=3001214 width=44)
-> Merge Join (cost=121139.01..199238.80 rows=3001214 width=44)
Merge Cond: (cp.cp_key = pr.cp_key)
-> Index Only Scan using catprod_pk on catprod cp
(cost=0.00..32975.13 rows=44989 width=8)
-> Sort (cost=121138.54..121698.84 rows=224119 width=52)
Sort Key: pr.cp_key
->
Seq Scan on producto pr (cost=0.00..101221.19 rows=224119 width=52) (37 rows) dbcom=# CREATE TEMP
TABLE PRTEMP AS SELECT * FROM PRODUCTO WHERE PR_ESTADO = 1; SELECT 224119 dbcom=# CREATE TEMP
TABLE PPTEMP AS SELECT * FROM PROPRO WHERE PV_KEY = 3268; SELECT 3653 dbcom=# explain
SELECT COALESCE(SUM(VT.VM_CANTIDAD),0) AS UNITS, COALESCE(SUM(VT.VM_MTO_PC),0)
AS MONTOPC, COALESCE(SUM(VT.VM_MTO_PV),0) AS MONTOPV FROM PPTEMP PP ,PRTEMP PR
,VENTAMC VT ,LOCAL LO ,LOCPROV LP WHERE PP.PR_KEY = PR.PR_KEY
AND PP.PR_KEY = VT.PR_KEY AND VT.LO_KEY = LO.LO_KEY AND LO.LO_KEY = LP.LO_KEY
AND LP.PV_KEY = PP.PV_KEY AND VT.PE_KEY >= 72 AND VT.PE_KEY <= 82;
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate
(cost=12705609.96..12705609.97 rows=1 width=17)
-> Merge Join (cost=6095609.99..10502328.76 rows=293770827
width=17)
Merge Cond: (pr.pr_key = pp.pr_key)
-> Sort (cost=101482.89..101557.13 rows=29694 width=8)
Sort Key: pr.pr_key
-> Seq Scan on prtemp pr (cost=0.00..99276.94 rows=29694
width=8)
-> Sort (cost=5994127.10..5999073.75 rows=1978658 width=33)
Sort Key: pp.pr_key
-> Merge Join (cost=4421695.74..5787198.15 rows=1978658
width=33)
Merge Cond: ((lp.lo_key = vt.lo_key) AND (lp.pv_key = pp.pv_key))
-> Index Only Scan using locprov_pk on locprov lp
(cost=0.00..1769448.68 rows=7310743 width=16)
-> Sort (cost=4419343.24..4424255.45 rows=1964886 width=57)
Sort Key: vt.lo_key, pp.pv_key
-> Hash Join (cost=298.81..4213953.57 rows=1964886 width=57)
Hash Cond: (vt.lo_key = lo.lo_key)
-> Nested Loop
(cost=0.00..4186637.57 rows=1964886 width=49)
-> Seq Scan on pptemp pp (cost=0.00..732.24 rows=1224 width=16)
-> Append (cost=0.00..3401.41 rows=1845 width=33)
-> Seq Scan on ventamc vt (cost=0.00..0.00 rows=1 width=64)
Filter: ((pe_key >= 72) AND (pe_key <= 82) AND (pp.pr_key = pr_key))
->
Index Scan using ventamc_72_pr_key_idx on ventamc_72 vt
(cost=0.00..368.73 rows=166 width=32)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
-> Index Scan using ventamc_73_pr_key_idx on ventamc_73 vt
(cost=0.00..362.39 rows=167 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
-> Index Scan using ventamc_74_pr_key_idx on ventamc_74 vt
(cost=0.00..371.32 rows=169 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
-> Index Scan using ventamc_75_pr_key_idx on ventamc_75 vt
(cost=0.00..366.24 rows=169 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
-> Index Scan using ventamc_76_pr_key_idx on ventamc_76 vt
(cost=0.00..365.37 rows=169 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter:
((pe_key >= 72) AND (pe_key <= 82))
-> Index Scan using ventamc_77_pr_key_idx on ventamc_77 vt
(cost=0.00..363.91 rows=170 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
-> Index Scan using ventamc_78_pr_key_idx on ventamc_78 vt
(cost=0.00..367.02 rows=170 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
-> Index Scan using ventamc_79_pr_key_idx on ventamc_79 vt
(cost=0.00..359.19 rows=167 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
-> Index Scan using ventamc_80_pr_key_idx on ventamc_80 vt
(cost=0.00..353.77 rows=165 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter:
((pe_key >= 72) AND (pe_key <= 82))
-> Index Scan using ventamc_81_pr_key_idx on ventamc_81 vt
(cost=0.00..61.72 rows=167 width=33)
Index
Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
-> Index Scan using ventamc_82_pr_key_idx on ventamc_82 vt (cost=0.00..61.74
rows=165 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
-> Hash (cost=288.36..288.36 rows=836 width=8)
-> Seq Scan on local lo (cost=0.00..288.36 rows=836 width=8) (55 rows) dbcom=# explain WITH
PRTEMP AS (SELECT * FROM PRODUCTO WHERE PR_ESTADO = 1 ), PPTEMP AS
(SELECT * FROM PROPRO WHERE PV_KEY = 3268) SELECT
COALESCE(SUM(VT.VM_CANTIDAD),0) AS UNITS, COALESCE(SUM(VT.VM_MTO_PC),0) AS
MONTOPC, COALESCE(SUM(VT.VM_MTO_PV),0) AS MONTOPV FROM PPTEMP PP ,PRTEMP PR
,VENTAMC VT ,LOCAL LO ,LOCPROV LP WHERE PP.PR_KEY = PR.PR_KEY
AND PP.PR_KEY = VT.PR_KEY AND VT.LO_KEY = LO.LO_KEY AND LO.LO_KEY = LP.LO_KEY
AND LP.PV_KEY = PP.PV_KEY AND VT.PE_KEY >= 72 AND VT.PE_KEY <= 82;
QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ Aggregate
(cost=146624535.65..146624535.66 rows=1 width=17) CTE
prtemp
-> Seq Scan on producto (cost=0.00..101781.49 rows=224119
width=141)
Filter: (pr_estado = 1) CTE
pptemp
-> Index Scan using propro_pv_key_idx on propro
(cost=0.00..46293.87 rows=3407 width=46)
Index Cond: (pv_key = 3268)
-> Merge Join (cost=7610435.83..100188156.44 rows=6171773848
width=17)
Merge Cond: (pr.pr_key = pp.pr_key)
-> Sort (cost=24399.73..24960.03 rows=224119 width=8)
Sort Key: pr.pr_key
-> CTE Scan on prtemp pr (cost=0.00..4482.38 rows=224119
width=8)
-> Sort (cost=7586036.10..7599805.07 rows=5507586 width=33)
Sort Key: pp.pr_key
-> Hash Join (cost=5466391.59..6969379.55 rows=5507586 width=33)
Hash Cond: (vt.lo_key = lo.lo_key)
-> Merge Join (cost=5466092.78..6893351.43 rows=5507586
width=49)
Merge Cond: ((lp.lo_key = vt.lo_key) AND (lp.pv_key = pp.pv_key))
-> Index Only Scan using locprov_pk on locprov lp
(cost=0.00..1769448.68 rows=7310743 width=16)
-> Sort (cost=5463740.28..5477413.41 rows=5469253 width=49)
Sort Key: vt.lo_key, pp.pv_key
-> Nested Loop (cost=0.00..4851651.22 rows=5469253 width=49)
-> CTE Scan on pptemp pp (cost=0.00..68.14 rows=3407 width=16)
-> Append (cost=0.00..1405.55 rows=1845 width=33)
-> Seq Scan on ventamc vt (cost=0.00..0.00 rows=1 width=64)
Filter: ((pe_key >= 72) AND (pe_key <= 82) AND (pp.pr_key = pr_key))
-> Index Scan using ventamc_72_pr_key_idx on ventamc_72 vt
(cost=0.00..145.41 rows=166 width=32)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
->
Index Scan using ventamc_73_pr_key_idx on ventamc_73 vt (cost=0.00..143.07
rows=167 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
-> Index Scan using ventamc_74_pr_key_idx on ventamc_74 vt
(cost=0.00..146.39 rows=169 width=33)
Index
Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
-> Index Scan using ventamc_75_pr_key_idx on ventamc_75 vt
(cost=0.00..144.52 rows=169 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
->
Index Scan using ventamc_76_pr_key_idx on ventamc_76 vt
(cost=0.00..144.19 rows=169 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
-> Index Scan using ventamc_77_pr_key_idx on ventamc_77 vt
(cost=0.00..143.68 rows=170 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
-> Index Scan using ventamc_78_pr_key_idx on ventamc_78 vt
(cost=0.00..144.82 rows=170 width=33)
Index
Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
-> Index Scan using ventamc_79_pr_key_idx on ventamc_79 vt
(cost=0.00..141.87 rows=167 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
->
Index Scan using ventamc_80_pr_key_idx on ventamc_80 vt (cost=0.00..139.84
rows=165 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND
(pe_key <= 82))
-> Index Scan using ventamc_81_pr_key_idx on ventamc_81 vt
(cost=0.00..55.84 rows=167 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
-> Index Scan using ventamc_82_pr_key_idx on ventamc_82 vt
(cost=0.00..55.92 rows=165 width=33)
Index Cond: (pr_key = pp.pr_key)
Filter: ((pe_key >= 72) AND (pe_key <= 82))
-> Hash (cost=288.36..288.36 rows=836 width=8)
-> Seq Scan on local lo (cost=0.00..288.36 rows=836 width=8) (61 rows) regards Thanks in advance Cristian Iturrieta |
