-bash-2.05b$ psql CCM Welcome to psql 8.2.3, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help with psql commands \g or terminate with semicolon to execute query \q to quit CCM=# VACUUM FULL ANALYZE ; VACUUM CCM=# explain ANALYZE SELECT distinct C.cod_couleur_panneau, C.cod_couleur_panneau, cast ('LM05' as varchar), cast ('OMM_TEINTE' as varchar), cast ('IM' as varchar) FROM lm05_t_modele AS A, lm05_t_couleur_panneau AS C, lm05_t_infos_modele AS D, lm05_t_tarif_panneau AS G , lm05_t_composition AS E , lm05_t_couleur_profile AS F , cm_gestion_modele_ca as H, mag_gestion_modele_mag as I WHERE A.cod_type_ouverture = 'OUV_COU' AND A.cod_type_panneau = 'PAN_MEL' AND A.cod_modele = C.cod_modele AND A.cod_modele = D.cod_modele AND A.cod_modele = G.cod_modele AND G.cod_tarif_panneau = C.cod_tarif_panneau AND A.cod_modele = E.cod_modele AND nb_vantaux >= 2 AND A.cod_modele = F.cod_modele AND F.couleur_profile = 'acajou mat' AND F.cod_tarif_profile = G.cod_tarif_profile AND A.cod_fournisseur = '5132' AND A.cod_gamme_prof = 'Design Xtra' AND C.ht_min < 2000 AND C.ht_max >= 2000 AND D.largeur_maxi_rail >= 1000 AND C.cod_aspect = 'tons bois et cuirs' AND C.cod_gamme_panneau = 'BOIS et CUIR XTRA 3' AND ((G.lrg_min < 1000 AND G.lrg_max >= 1000) OR (G.lrg_min < 500 AND G.lrg_max >= 500) OR (G.lrg_min < 333.333333333333 AND G.lrg_max >= 333.333333333333) OR (G.lrg_min < 250 AND G.lrg_max >= 250) OR (G.lrg_min < 200 AND G.lrg_max >= 200) OR (G.lrg_min < 166.666666666667 AND G.lrg_max >= 166.666666666667) OR (G.lrg_min < 142.857142857143 AND G.lrg_max >= 142.857142857143) OR (G.lrg_min < 125 AND G.lrg_max >= 125) OR (G.lrg_min < 111.111111111111 AND G.lrg_max >= 111.111111111111) OR (G.lrg_min < 100 AND G.lrg_max >= 100)) AND H.idmagasin = '011' AND H.idoav='PC_PLACARD' AND H.cod_modele = A.cod_modele AND H.autorise = 1 AND I.idmagasin = '011' AND I.idoav='PC_PLACARD' AND I.cod_modele = A.cod_modele AND I.selection = 1; QUERY PLAN -------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------- Unique (cost=5276.93..5276.95 rows=1 width=32) (actual time=28977.716..28980.225 rows=140 loops=1) -> Sort (cost=5276.93..5276.94 rows=1 width=32) (actual time=28977.712..28978.464 rows=1400 loops=1) Sort Key: c.cod_couleur_panneau, c.cod_couleur_panneau, 'LM05'::character varying, 'OMM_TEINTE'::character varying, 'IM'::character varying -> Nested Loop (cost=6.31..5276.92 rows=1 width=32) (actual time=111.982..28945.376 rows=1400 loops=1) Join Filter: (a.cod_modele = d.cod_modele) -> Nested Loop (cost=6.31..5268.80 rows=1 width=60) (actual time=111.790..28626.234 rows=1400 loops=1) Join Filter: (a.cod_modele = e.cod_modele) -> Nested Loop (cost=6.31..5246.81 rows=1 width=56) (actual time=111.026..28406.507 rows=280 loops=1) Join Filter: ((a.cod_modele = f.cod_modele) AND ((f.cod_tarif_profile)::text = (g.cod_tarif_profile)::text)) -> Nested Loop (cost=6.31..5147.63 rows=1 width=70) (actual time=94.114..27028.607 rows=1120 loops=1) Join Filter: (h.cod_modele = a.cod_modele) -> Nested Loop (cost=6.31..4634.34 rows=1 width=66) (actual time=87.369..20023.134 rows=1120 loops=1) Join Filter: (i.cod_modele = a.cod_modele) -> Nested Loop (cost=6.31..4623.51 rows=1 width=62) (actual time=87.018..19664.375 rows=1120 loops=1) Join Filter: ((a.cod_modele = c.cod_modele) AND ((g.cod_tarif_panneau)::text = (c.cod_tarif_panneau)::text)) -> Seq Scan on lm05_t_couleur_panneau c (cost=0.00..1565.60 rows=1 width=62) (actual time=23.551..28.649 rows=280 loops=1) Filter: ((ht_min < 2000) AND (ht_max >= 2000) AND ((cod_aspect)::text = 'tons bois et cuirs'::text) AND ((cod_gamme_panneau)::text = 'BOIS et CUIR XTRA 3'::text)) -> Hash Join (cost=6.31..3056.17 rows=116 width=47) (actual time=60.055..70.078 rows=48 loops=280) Hash Cond: (g.cod_modele = a.cod_modele) -> Seq Scan on lm05_t_tarif_panneau g (cost=0.00..2977.08 rows=19097 width=43) (actual time=0.008..67.670 rows=4062 loops=280) Filter: (((lrg_min < 1000) AND (lrg_max >= 1000)) OR ((lrg_min < 500) AND (lrg_max >= 500)) OR (((lrg_min)::numeric < 333.333333333333) AND ((lrg_max)::numeric >= 333.333333333333)) OR ((lrg_min < 250) AND (lrg_max >= 250)) OR ((lrg_min < 200) AND (lrg_max >= 200)) OR (((lrg_min)::numeric < 166.666666666667) AND ((lrg_max)::numeric >= 166.666666666667)) OR (((lrg_min)::numeric < 142.857142857143) AND ((lrg_max)::numeric >= 142.857142857143)) OR ((lrg_min < 125) AND (lrg_max >= 125)) OR (((lrg_min)::numeric < 111.111111111111) AND ((lrg_max)::numeric >= 111.111111111111)) OR ((lrg_min < 100) AND (lrg_max >= 100))) -> Hash (cost=6.30..6.30 rows=1 width=4) (actual time=0.114..0.114 rows=1 loops=1) -> Seq Scan on lm05_t_modele a (cost=0.00..6.30 rows=1 width=4) (actual time=0.040..0.109 rows=1 loops=1) Filter: (((cod_type_ouverture)::text = 'OUV_COU'::text) AND ((cod_type_panneau)::text = 'PAN_MEL'::text) AND (cod_fournisseur = 5132) AND ((cod_gamme_prof)::text = 'Design Xtra'::text)) -> Seq Scan on mag_gestion_modele_mag i (cost=0.00..8.78 rows=165 width=4) (actual time=0.058..0.220 rows=165 loops=1120) Filter: (((idmagasin)::text = '011'::text) AND ((idoav)::text = 'PC_PLACARD'::text) AND (selection = 1)) -> Seq Scan on cm_gestion_modele_ca h (cost=0.00..511.27 rows=161 width=4) (actual time=0.030..6.152 rows=165 loops=1120) Filter: (((idmagasin)::text = '011'::text) AND ((idoav)::text = 'PC_PLACARD'::text) AND (autorise = 1)) -> Seq Scan on lm05_t_couleur_profile f (cost=0.00..98.86 rows=21 width=22) (actual time=0.849..1.205 rows=32 loops=1120) Filter: ((couleur_profile)::text = 'acajou mat'::text) -> Seq Scan on lm05_t_composition e (cost=0.00..14.82 rows=573 width=4) (actual time=0.010..0.465 rows=573 loops=280) Filter: (nb_vantaux >= 2) -> Seq Scan on lm05_t_infos_modele d (cost=0.00..6.06 rows=165 width=4) (actual time=0.004..0.136 rows=165 loops=1400) Filter: (largeur_maxi_rail >= 1000) Total runtime: 28980.630 ms (35 rows) CCM=# -bash-2.05b$ psql CCM Welcome to psql 7.3.10-RH, the PostgreSQL interactive terminal. Type: \copyright for distribution terms \h for help with SQL commands \? for help on internal slash commands \g or terminate with semicolon to execute query \q to quit CCM=# EXPLAIN ANALYZE SELECT distinct C.cod_couleur_panneau, C.cod_couleur_panneau, cast ('LM05' as varchar), cast ('OMM_TEINTE' as varchar), cast ('IM' as varchar) FROM lm05_t_modele AS A, lm05_t_couleur_panneau AS C, lm05_t_infos_modele AS D, lm05_t_tarif_panneau AS G , lm05_t_composition AS E , lm05_t_couleur_profile AS F , cm_gestion_modele_ca as H, mag_gestion_modele_mag as I WHERE A.cod_type_ouverture = 'OUV_COU' AND A.cod_type_panneau = 'PAN_MEL' AND A.cod_modele = C.cod_modele AND A.cod_modele = D.cod_modele AND A.cod_modele = G.cod_modele AND G.cod_tarif_panneau = C.cod_tarif_panneau AND A.cod_modele = E.cod_modele AND nb_vantaux >= 2 AND A.cod_modele = F.cod_modele AND F.couleur_profile = 'acajou mat' AND F.cod_tarif_profile = G.cod_tarif_profile AND A.cod_fournisseur = '5132' AND A.cod_gamme_prof = 'Design Xtra' AND C.ht_min < 2000 AND C.ht_max >= 2000 AND D.largeur_maxi_rail >= 1000 AND C.cod_aspect = 'tons bois et cuirs' AND C.cod_gamme_panneau = 'BOIS et CUIR XTRA 3' AND ((G.lrg_min < 1000 AND G.lrg_max >= 1000) OR (G.lrg_min < 500 AND G.lrg_max >= 500) OR (G.lrg_min < 333.333333333333 AND G.lrg_max >= 333.333333333333) OR (G.lrg_min < 250 AND G.lrg_max >= 250) OR (G.lrg_min < 200 AND G.lrg_max >= 200) OR (G.lrg_min < 166.666666666667 AND G.lrg_max >= 166.666666666667) OR (G.lrg_min < 142.857142857143 AND G.lrg_max >= 142.857142857143) OR (G.lrg_min < 125 AND G.lrg_max >= 125) OR (G.lrg_min < 111.111111111111 AND G.lrg_max >= 111.111111111111) OR (G.lrg_min < 100 AND G.lrg_max >= 100)) AND H.idmagasin = '011' AND H.idoav='PC_PLACARD' AND H.cod_modele = A.cod_modele AND H.autorise = 1 AND I.idmagasin = '011' AND I.idoav='PC_PLACARD' AND I.cod_modele = A.cod_modele AND I.selection = 1; QUERY PLAN -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------------------------------------------------- -------------------------------------- Unique (cost=232.48..232.51 rows=1 width=497) (actual time=821.34..839.75 rows=140 loops=1) -> Sort (cost=232.48..232.49 rows=2 width=497) (actual time=821.34..822.06 rows=1400 loops=1) Sort Key: c.cod_couleur_panneau, c.cod_couleur_panneau, 'LM05'::character varying, 'OMM_TEINTE'::character varying, 'IM'::character varying -> Hash Join (cost=208.29..232.47 rows=2 width=497) (actual time=777.53..780.99 rows=1400 loops=1) Hash Cond: ("outer".cod_modele = "inner".cod_modele) -> Seq Scan on lm05_t_infos_modele d (cost=0.00..22.50 rows=333 width=4) (actual time=3.93..4.46 rows=165 loops=1) Filter: (largeur_maxi_rail >= 1000) -> Hash (cost=208.28..208.28 rows=1 width=493) (actual time=772.34..772.34 rows=0 loops=1) -> Hash Join (cost=184.09..208.28 rows=1 width=493) (actual time=582.72..770.73 rows=1400 loops=1) Hash Cond: ("outer".cod_tarif_profile = "inner".cod_tarif_profile) Join Filter: ("inner".cod_modele = "outer".cod_modele) -> Hash Join (cost=161.57..185.76 rows=2 width=396) (actual time=535.31..549.63 rows=5600 loops=1) Hash Cond: ("outer".cod_modele = "inner".cod_modele) -> Seq Scan on lm05_t_composition e (cost=0.00..22.50 rows=333 width=4) (actual time=1.75..3.23 rows=573 loops=1) Filter: (nb_vantaux >= 2) -> Hash (cost=161.57..161.57 rows=1 width=392) (actual time=531.89..531.89 rows=0 loops=1) -> Hash Join (cost=68.10..161.57 rows=1 width=392) (actual time=418.18..530.57 rows=1120 loops=1) Hash Cond: ("outer".cod_modele = "inner".cod_modele) Join Filter: ("outer".cod_tarif_panneau = "inner".cod_tarif_panneau) -> Hash Join (cost=32.35..125.82 rows=1 width=198) (actual time=229.95..333.14 rows=48 loops=1) Hash Cond: ("outer".cod_modele = "inner".cod_modele) -> Seq Scan on lm05_t_tarif_panneau g (cost=0.00..90.00 rows=692 width=190) (actual time=0.31..295.15 rows=4062 loops=1) Filter: (((lrg_min < 1000) AND (lrg_max >= 1000)) OR ((lrg_min < 500) AND (lrg_max >= 500)) OR (((lrg_min)::numeric < 333.333333333333) AND ((lrg_max)::numeric >= 333.333333333333)) OR ((lrg_min < 250) AND (lrg_max >= 250)) OR ((lrg_min < 200) AND (lrg_max >= 200)) OR (((lrg_min)::numeric <166.666666666667) AND ((lrg_max)::numeric >= 166.666666666667)) OR (((lrg_min)::numeric < 142.857142857143) AND ((lrg_max)::numeric >= 142.857142857143)) OR ((lrg_min < 125) AND (lrg_max >= 125)) OR (((lrg_min)::numeric < 111.111111111111) AND ((lrg_max)::numeric >= 111.111111111111)) OR ((lrg_min < 100) AND (lrg_max >= 100))) -> Hash (cost=32.35..32.35 rows=1 width=8) (actual time=34.88..34.88 rows=0 loops=1) -> Nested Loop (cost=0.00..32.35 rows=1 width=8) (actual time=33.34..34.87 rows=1 loops=1) -> Seq Scan on cm_gestion_modele_ca h (cost=0.00..27.50 rows=1 width=4) (actual time=0.14..25.64 rows=165 loops=1) Filter: ((idmagasin = '011'::character varying) AND (idoav = 'PC_PLACARD'::character varying) AND (autorise = 1)) -> Index Scan using lm05_t_modele_cod_modele_key on lm05_t_modele a (cost=0.00..4.83 rows=1 width=4) (actual time=0.05..0.05 rows=0 loops=165) Index Cond: ("outer".cod_modele = a.cod_modele) Filter: ((cod_type_ouverture = 'OUV_COU'::character varying) AND (cod_type_panneau = 'PAN_MEL'::character varying) AND (cod_fournisseur = 5132) AND (cod_gamme_prof = 'Design Xtra'::character varying)) -> Hash (cost=35.75..35.75 rows=1 width=194) (actual time=186.04..186.04 rows=0 loops=1) -> Hash Join (cost=30.00..35.75 rows=1 width=194) (actual time=185.26..185.77 rows=280 loops=1) Hash Cond: ("outer".cod_modele = "inner".cod_modele) -> Seq Scan on mag_gestion_modele_mag i (cost=0.00..4.91 rows=166 width=4) (actual time=0.05..1.15 rows=166 loops=1) Filter: ((idmagas in = '011'::character varying) AND (idoav = 'PC_PLACARD'::character varying) AND (selection = 1)) -> Hash (cost=30.00..30.00 rows=1 width=190) (actual time=183.99..183.99 rows=0 loops=1) -> Seq Scan on lm05_t_couleur_panneau c (cost=0.00..30.00 rows=1 width=190) (actual time=146.95..183.67 rows=280 loops=1) Filter: ((ht_min < 2000) AND (ht_max >= 2000) AND (cod_aspect = 'tons bois et cuirs'::character varying) AND (cod_gamme_panneau = 'BOIS et CUIR XTRA 3'::character varying)) -> Hash (cost=22.50..22.50 rows=5 width=97) (actual time=46.82..46.82 rows=0 loops=1) -> Seq Scan on lm05_t_couleur_profile f (cost=0.00..22.50 rows=5 width=97) (actual time=32.07..46.77 rows=32 loops=1) Filter: (couleur_profile = 'acajou mat'::character varying) Total runtime: 840.40 msec (42 rows) CCM=#