Home > mailing lists

Postgresql 9.2, Memoy cache usage. - Mailing list pgsql-general

From	Cristian Iturrieta
Subject	Postgresql 9.2, Memoy cache usage.
Date	December 18, 2014 22:52:52
Msg-id	015c01d01aeb$093f8960$1bbe9c20$@cl Whole thread Raw
List	pgsql-general

Tree view

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;

--------+-------------+---------------+------------------------------------------+-------------------+--------------------------+--------------+---------------------

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

--------+-------------+-------------+------------------------------------------+-------------------+--------------------------+--------------+---------------------

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)

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

pgsql-general by date:

From: "Zuiderhoek, Jeroen"
Date: 18 December 2014, 22:45:51
Subject: Building debug version with MSVC 2013 for 64bit: No _d postfix on dll and lib files...

From: Merlin Moncure
Date: 18 December 2014, 22:59:53
Subject: Re: pl/pgsql trigger function - compare *most* columns in NEW vs. OLD

Postgresql 9.2, Memoy cache usage. - Mailing list pgsql-general

Previous

Next