Vous n'êtes pas identifié(e).
Pages : 1
Bonjour,
J'utilise interspire email marketer avec postgresql. Mais je trouve qu'il rame pas mal.
J'ai une machine assez puissante avec 12 Gb de mémoire.
Mais le temps de certaines requete est impressionnant.
En voila un exemple :
SELECT
c.fieldid AS fieldid,
c.name AS fieldname,
c.fieldtype AS fieldtype,
c.fieldsettings AS fieldsettings,
d.subscriberid AS subscriberid,
d.data AS data
FROM
email_customfields AS c
JOIN email_customfield_lists AS cl
ON (
c.fieldid = cl.fieldid
AND cl.listid IN (1)
)
JOIN email_list_subscribers AS ls
ON (
cl.listid = ls.listid
AND ls.listid IN (1)
AND ls.subscriberid IN
(1634516,1634515,1634514,1634513,1634512,1634511,1634510,1634509,1634508,1634507,1634506,1634505,1634504,1634503,1634502,1634501,1634500,1634499,1634498,1634497,1634496,1634495,1634494,1634493,1634492,1634491,1634490,1634489,1634488,1634487,1634486,1634485,1634484,1634483,1634482,1634481,1634480,1634479,1634478,1634477,1634476,1634474,1634473,1634472,1634471,1634470,1634469,1634468,1634467,1634466,1634465,1634464,1634463,1634462,1634461,1634460,1634459,1634458,1634457,1634456,1634455,1634454,1634453,1634452,1634451,1634450,1634449,1634448,1634447,1634446,1634445,1634444,1634443,1634442,1634441,1634329,1634328,1634327,1634326,1634325,1634324,1634323,1634322,1634321,1634320,1634319,1634318,1634317,1634316,1634315,1634314,1634313,1634312,1634311,1634310,1634309,1634308,1634307,1634306,1634305,1634304,1634639,1634638,1634637,1634636,1634635,1634634,1634633,1634632,1634631,1634630,1634629,1634628,1634627,1634626,1634625,1634624,1634623,1634622,1634621,1634620,1634619,1634618,1634617,1634616,1634615,1634614,1634613,1634612,1634611,1634610,1634609,1634608,1634607,1634606,1634605,1634604,1634603,1634602,1634601,1634600,1634599,1634598,1634303,1634302,1634301,1634300,1634299,1634298,1634297,1634296,1634295,1634294,1634293,1634292,1634291,1634290,1634289,1634288,1634287,1634286,1634285,1634284,1634283,1634282,1634281,1634280,1634279,1634278,1634277,1634276,1634275,1634274,1634273,1634272,1634271,1634270,1634269,1634268,1634267,1634266,1634265,1634264,1634263,1634262,1634261,1634260,1634259,1634258,1634257,1634256,1634255,1634254,1634253,1634252,1634251,1634250,1634249,1634248,1634247,1634246,1634245,1634244,1634243,1634242,1634241,1634240,1634239,1634238,1634237,1634236,1634235,1634234,1634233,1634232,1634231,1634230,1634229,1634228,1634227,1634226,1634225,1634224,1634223,1634222,1634221,1634220,1634219,1634218,1634217,1634216,1634215,1634214,1634213,1634212,1634211,1634210,1634209,1634208,1634207,1634206,1634204,1634203,1634202,1634201,1634200,1634199,1634198,1634197,1634195,1634194,1634193,1634192,1634191,1634190,1634189,1634188,1634187,1634186,1634185,1634184,1634183,1634182,1634181,1634180,1634179,1634178,1634177,1634176,1634175,1634174,1634119,1634118,1634117,1634116,1634115,1634114,1634113,1634112,1634111,1634110,1634109,1634108,1634107,1634106,1634105,1634104,1634103,1634102,1634101,1634100,1634099,1634098,1634097,1634096,1634095,1634094,1634440,1634439,1634438,1634437,1634435,1634434,1634433,1634432,1634431,1634430,1634429,1634428,1634427,1634426,1634425,1634424,1634423,1634422,1634421,1634420,1634419,1634418,1634417,1634416,1634415,1634414,1634412,1634411,1634410,1634409,1634408,1634407,1634406,1634405,1634404,1634403,1634402,1634401,1634400,1634399,1634398,1634397,1634396,1634395,1634394,1634393,1634392,1634391,1634390,1634389,1634388,1634387,1634386,1634385,1634384,1634383,1634382,1634381,1634380,1634379,1634378,1634377,1634376,1634375,1634374,1634373,1634372,1634371,1634370,1634369,1634368,1634367,1634366,1634365,1634364,1634363,1634362,1634361,1634360,1634359,1634358,1634357,1634356,1634355,1634354,1634353,1634352,1634350,1634349,1634348,1634347,1634346,1634345,1634344,1634343,1634342,1634341,1634340,1634339,1634338,1634337,1634336,1634335,1634334,1634333,1634332,1634331,1634330,1634093,1634092,1634091,1634090,1634089,1634088,1634087,1634086,1634085,1634084,1634083,1634082,1634081,1634080,1634079,1634078,1634077,1634076,1634075,1634074,1634073,1634072,1634071,1634070,1634069,1634068,1634067,1634066,1634065,1634064,1634063,1634062,1634061,1634060,1634059,1634058,1634057,1634056,1634055,1634054,1634053,1634052,1634051,1634050,1634049,1634048,1634047,1634046,1634045,1634044,1634043,1634042,1634041,1634040,1634039,1634038,1634037,1634036,1634034,1634033,1634032,1634031,1634030,1634029,1634028,1634027,1634026,1634025,1634024,1634023,1634022,1634021,1634020,1634019,1634018,1634017,1634016,1634015,1634014,1634013,1634012,1634011,1634010,1634009,1634008,1634007,1634006,1634005,1634004,1634003,1634002,1634001,1634000,1633999,1633998)
)
JOIN email_subscribers_data AS d
ON (
ls.subscriberid = d.subscriberid
AND cl.fieldid = d.fieldid
AND c.fieldid = d.fieldid
)
WHERE
(c.name IN ('First Name','Last Clicked Mail','Last Name','Last Open Mail','Mobile','Postal/Zip Code') OR c.fieldid IN
(2,3))
AND d.subscriberid IN
(1634516,1634515,1634514,1634513,1634512,1634511,1634510,1634509,1634508,1634507,1634506,1634505,1634504,1634503,1634502,1634501,1634500,1634499,1634498,1634497,1634496,1634495,1634494,1634493,1634492,1634491,1634490,1634489,1634488,1634487,1634486,1634485,1634484,1634483,1634482,1634481,1634480,1634479,1634478,1634477,1634476,1634474,1634473,1634472,1634471,1634470,1634469,1634468,1634467,1634466,1634465,1634464,1634463,1634462,1634461,1634460,1634459,1634458,1634457,1634456,1634455,1634454,1634453,1634452,1634451,1634450,1634449,1634448,1634447,1634446,1634445,1634444,1634443,1634442,1634441,1634329,1634328,1634327,1634326,1634325,1634324,1634323,1634322,1634321,1634320,1634319,1634318,1634317,1634316,1634315,1634314,1634313,1634312,1634311,1634310,1634309,1634308,1634307,1634306,1634305,1634304,1634639,1634638,1634637,1634636,1634635,1634634,1634633,1634632,1634631,1634630,1634629,1634628,1634627,1634626,1634625,1634624,1634623,1634622,1634621,1634620,1634619,1634618,1634617,1634616,1634615,1634614,1634613,1634612,1634611,1634610,1634609,1634608,1634607,1634606,1634605,1634604,1634603,1634602,1634601,1634600,1634599,1634598,1634303,1634302,1634301,1634300,1634299,1634298,1634297,1634296,1634295,1634294,1634293,1634292,1634291,1634290,1634289,1634288,1634287,1634286,1634285,1634284,1634283,1634282,1634281,1634280,1634279,1634278,1634277,1634276,1634275,1634274,1634273,1634272,1634271,1634270,1634269,1634268,1634267,1634266,1634265,1634264,1634263,1634262,1634261,1634260,1634259,1634258,1634257,1634256,1634255,1634254,1634253,1634252,1634251,1634250,1634249,1634248,1634247,1634246,1634245,1634244,1634243,1634242,1634241,1634240,1634239,1634238,1634237,1634236,1634235,1634234,1634233,1634232,1634231,1634230,1634229,1634228,1634227,1634226,1634225,1634224,1634223,1634222,1634221,1634220,1634219,1634218,1634217,1634216,1634215,1634214,1634213,1634212,1634211,1634210,1634209,1634208,1634207,1634206,1634204,1634203,1634202,1634201,1634200,1634199,1634198,1634197,1634195,1634194,1634193,1634192,1634191,1634190,1634189,1634188,1634187,1634186,1634185,1634184,1634183,1634182,1634181,1634180,1634179,1634178,1634177,1634176,1634175,1634174,1634119,1634118,1634117,1634116,1634115,1634114,1634113,1634112,1634111,1634110,1634109,1634108,1634107,1634106,1634105,1634104,1634103,1634102,1634101,1634100,1634099,1634098,1634097,1634096,1634095,1634094,1634440,1634439,1634438,1634437,1634435,1634434,1634433,1634432,1634431,1634430,1634429,1634428,1634427,1634426,1634425,1634424,1634423,1634422,1634421,1634420,1634419,1634418,1634417,1634416,1634415,1634414,1634412,1634411,1634410,1634409,1634408,1634407,1634406,1634405,1634404,1634403,1634402,1634401,1634400,1634399,1634398,1634397,1634396,1634395,1634394,1634393,1634392,1634391,1634390,1634389,1634388,1634387,1634386,1634385,1634384,1634383,1634382,1634381,1634380,1634379,1634378,1634377,1634376,1634375,1634374,1634373,1634372,1634371,1634370,1634369,1634368,1634367,1634366,1634365,1634364,1634363,1634362,1634361,1634360,1634359,1634358,1634357,1634356,1634355,1634354,1634353,1634352,1634350,1634349,1634348,1634347,1634346,1634345,1634344,1634343,1634342,1634341,1634340,1634339,1634338,1634337,1634336,1634335,1634334,1634333,1634332,1634331,1634330,1634093,1634092,1634091,1634090,1634089,1634088,1634087,1634086,1634085,1634084,1634083,1634082,1634081,1634080,1634079,1634078,1634077,1634076,1634075,1634074,1634073,1634072,1634071,1634070,1634069,1634068,1634067,1634066,1634065,1634064,1634063,1634062,1634061,1634060,1634059,1634058,1634057,1634056,1634055,1634054,1634053,1634052,1634051,1634050,1634049,1634048,1634047,1634046,1634045,1634044,1634043,1634042,1634041,1634040,1634039,1634038,1634037,1634036,1634034,1634033,1634032,1634031,1634030,1634029,1634028,1634027,1634026,1634025,1634024,1634023,1634022,1634021,1634020,1634019,1634018,1634017,1634016,1634015,1634014,1634013,1634012,1634011,1634010,1634009,1634008,1634007,1634006,1634005,1634004,1634003,1634002,1634001,1634000,1633999,1633998)
) UNION (
SELECT
c.fieldid AS fieldid,
c.name AS fieldname,
c.fieldtype AS fieldtype,
c.fieldsettings AS fieldsettings,
ls.subscriberid AS subscriberid,
c.defaultvalue AS data
FROM
email_customfields AS c
JOIN email_customfield_lists AS cl
ON (
c.fieldid = cl.fieldid
AND cl.listid IN (1)
)
JOIN email_list_subscribers ls
ON (
cl.listid = ls.listid
AND ls.listid IN (1)
AND ls.subscriberid IN
(1634516,1634515,1634514,1634513,1634512,1634511,1634510,1634509,1634508,1634507,1634506,1634505,1634504,1634503,1634502,1634501,1634500,1634499,1634498,1634497,1634496,1634495,1634494,1634493,1634492,1634491,1634490,1634489,1634488,1634487,1634486,1634485,1634484,1634483,1634482,1634481,1634480,1634479,1634478,1634477,1634476,1634474,1634473,1634472,1634471,1634470,1634469,1634468,1634467,1634466,1634465,1634464,1634463,1634462,1634461,1634460,1634459,1634458,1634457,1634456,1634455,1634454,1634453,1634452,1634451,1634450,1634449,1634448,1634447,1634446,1634445,1634444,1634443,1634442,1634441,1634329,1634328,1634327,1634326,1634325,1634324,1634323,1634322,1634321,1634320,1634319,1634318,1634317,1634316,1634315,1634314,1634313,1634312,1634311,1634310,1634309,1634308,1634307,1634306,1634305,1634304,1634639,1634638,1634637,1634636,1634635,1634634,1634633,1634632,1634631,1634630,1634629,1634628,1634627,1634626,1634625,1634624,1634623,1634622,1634621,1634620,1634619,1634618,1634617,1634616,1634615,1634614,1634613,1634612,1634611,1634610,1634609,1634608,1634607,1634606,1634605,1634604,1634603,1634602,1634601,1634600,1634599,1634598,1634303,1634302,1634301,1634300,1634299,1634298,1634297,1634296,1634295,1634294,1634293,1634292,1634291,1634290,1634289,1634288,1634287,1634286,1634285,1634284,1634283,1634282,1634281,1634280,1634279,1634278,1634277,1634276,1634275,1634274,1634273,1634272,1634271,1634270,1634269,1634268,1634267,1634266,1634265,1634264,1634263,1634262,1634261,1634260,1634259,1634258,1634257,1634256,1634255,1634254,1634253,1634252,1634251,1634250,1634249,1634248,1634247,1634246,1634245,1634244,1634243,1634242,1634241,1634240,1634239,1634238,1634237,1634236,1634235,1634234,1634233,1634232,1634231,1634230,1634229,1634228,1634227,1634226,1634225,1634224,1634223,1634222,1634221,1634220,1634219,1634218,1634217,1634216,1634215,1634214,1634213,1634212,1634211,1634210,1634209,1634208,1634207,1634206,1634204,1634203,1634202,1634201,1634200,1634199,1634198,1634197,1634195,1634194,1634193,1634192,1634191,1634190,1634189,1634188,1634187,1634186,1634185,1634184,1634183,1634182,1634181,1634180,1634179,1634178,1634177,1634176,1634175,1634174,1634119,1634118,1634117,1634116,1634115,1634114,1634113,1634112,1634111,1634110,1634109,1634108,1634107,1634106,1634105,1634104,1634103,1634102,1634101,1634100,1634099,1634098,1634097,1634096,1634095,1634094,1634440,1634439,1634438,1634437,1634435,1634434,1634433,1634432,1634431,1634430,1634429,1634428,1634427,1634426,1634425,1634424,1634423,1634422,1634421,1634420,1634419,1634418,1634417,1634416,1634415,1634414,1634412,1634411,1634410,1634409,1634408,1634407,1634406,1634405,1634404,1634403,1634402,1634401,1634400,1634399,1634398,1634397,1634396,1634395,1634394,1634393,1634392,1634391,1634390,1634389,1634388,1634387,1634386,1634385,1634384,1634383,1634382,1634381,1634380,1634379,1634378,1634377,1634376,1634375,1634374,1634373,1634372,1634371,1634370,1634369,1634368,1634367,1634366,1634365,1634364,1634363,1634362,1634361,1634360,1634359,1634358,1634357,1634356,1634355,1634354,1634353,1634352,1634350,1634349,1634348,1634347,1634346,1634345,1634344,1634343,1634342,1634341,1634340,1634339,1634338,1634337,1634336,1634335,1634334,1634333,1634332,1634331,1634330,1634093,1634092,1634091,1634090,1634089,1634088,1634087,1634086,1634085,1634084,1634083,1634082,1634081,1634080,1634079,1634078,1634077,1634076,1634075,1634074,1634073,1634072,1634071,1634070,1634069,1634068,1634067,1634066,1634065,1634064,1634063,1634062,1634061,1634060,1634059,1634058,1634057,1634056,1634055,1634054,1634053,1634052,1634051,1634050,1634049,1634048,1634047,1634046,1634045,1634044,1634043,1634042,1634041,1634040,1634039,1634038,1634037,1634036,1634034,1634033,1634032,1634031,1634030,1634029,1634028,1634027,1634026,1634025,1634024,1634023,1634022,1634021,1634020,1634019,1634018,1634017,1634016,1634015,1634014,1634013,1634012,1634011,1634010,1634009,1634008,1634007,1634006,1634005,1634004,1634003,1634002,1634001,1634000,1633999,1633998)
)
LEFT JOIN email_subscribers_data d
ON (
ls.subscriberid = d.subscriberid
AND c.fieldid = d.fieldid
)
WHERE
(c.name IN ('First Name','Last Clicked Mail','Last Name','Last Open Mail','Mobile','Postal/Zip Code') OR c.fieldid IN
(2,3))
AND d.subscriberid IS null)Et le résultat de l'explain :
Unique (cost=22154.74..22162.34 rows=434 width=120) (actual time=338.186..339.659 rows=3000 loops=1)
-> Sort (cost=22154.74..22155.83 rows=434 width=120) (actual time=338.183..338.484 rows=3000 loops=1)
Sort Key: c.fieldid, c.name, c.fieldtype, c.fieldsettings, d.subscriberid, d.data
Sort Method: quicksort Memory: 706kB
-> Append (cost=2053.96..22135.73 rows=434 width=120) (actual time=1.256..19.985 rows=3000 loops=1)
-> Nested Loop (cost=2053.96..11065.70 rows=57 width=120) (actual time=1.256..9.026 rows=7 loops=1)
-> Nested Loop (cost=2053.96..3701.37 rows=754 width=113) (actual time=1.116..2.386 rows=3000 loops=1)
-> Bitmap Heap Scan on email_list_subscribers ls (cost=2050.95..3683.28 rows=377 width=8) (actual time=1.085..1.166 rows=500 loops=1)
Recheck Cond: ((subscriberid = ANY ('{1634516,1634515,1634514,1634513,1634512,1634511,1634510,1634509,1634508,1634507,1634506,1634505,1634504,1634503,1634502,1634501,1634500,1634499,1634498,1634497,1634496,1634495,1634494,1634493,1634492,1634491,1634490,1634489,1634488,1634487,1634
486,1634485,1634484,1634483,1634482,1634481,1634480,1634479,1634478,1634477,1634476,1634474,1634473,1634472,1634471,1634470,1634469,1634468,1634467,1634466,1634465,1634464,1634463,1634462,1634461,1634460,1634459,1634458,1634457,1634456,1634455,1634454,1634453,1634452,1634451,1634450,1634449,1634448,1634447,1634446,1634445,1634444,1634443,1634442,1634441,1634329,1634328,1634327,1634326,1634325,1634324,1634323,1634322,1634321,1634320,1634319,1634318,1634317,1634316,1634315,1634314,1634313,1634312,1634311,1634310,1634309,1634308,1634307,1634306,1634305,1634304,1634639,1634638,1634637,1634636,1634635,1634634,1634633,1634632,1634631,1634630,1634629,1634628,1634627,1634626,1634625,1634624,1634623,1634622,1634621,1634620,1634619,1634618,1634617,1634616,1634615,1634614,1634613,1634612,1634611,1634610,1634609,1634608,1634607,1634606,1634605,1634604,1634603,1634602,1634601,1634600,1634599,1634598,1634303,1634302,1634301,1634300,1634299,1634298,1634297,1634296,1634295,1634294,1634293,1634292,1634291,1634290,1634289,1634288,1634287,1634286,1634285,1634284,1634283,1634282,1634281,1634280,1634279,1634278,1634277,1634276,1634275,1634274,1634273,1634272,1634271,1634270,1634269,1634268,1634267,1634266,1634265,1634264,1634263,1634262,1634261,1634260,1634259,1634258,1634257,1634256,1634255,1634254,1634253,1634252,1634251,1634250,1634249,1634248,1634247,1634246,1634245,1634244,1634243,1634242,1634241,1634240,1634239,1634238,1634237,1634236,1634235,1634234,1634233,1634232,1634231,1634230,1634229,1634228,1634227,1634226,1634225,1634224,1634223,1634222,1634221,1634220,1634219,1634218,1634217,1634216,1634215,1634214,1634213,1634212,1634211,1634210,1634209,1634208,1634207,1634206,1634204,1634203,1634202,1634201,1634200,1634199,1634198,1634197,1634195,1634194,1634193,1634192,1634191,1634190,1634189,1634188,1634187,1634186,1634185,1634184,1634183,1634182,1634181,1634180,1634179,1634178,1634177,1634176,1634175,1634174,1634119,1634118,1634117,1634116,1634115,1634114,1634113,1634112,1634111,1634110,1634109,1634108,1634107,1634106,1634105,1634104,1634103,1634102,1634101,1634100,1634099,1634098,1634097,1634096,1634095,1634094,1634440,1634439,1634438,1634437,1634435,1634434,1634433,1634432,1634431,1634430,1634429,1634428,1634427,1634426,1634425,1634424,1634423,1634422,1634421,1634420,1634419,1634418,1634417,1634416,1634415,1634414,1634412,1634411,1634410,1634409,1634408,1634407,1634406,1634405,1634404,1634403,1634402,1634401,1634400,1634399,1634398,1634397,1634396,1634395,1634394,1634393,1634392,1634391,1634390,1634389,1634388,1634387,1634386,1634385,1634384,1634383,1634382,1634381,1634380,1634379,1634378,1634377,1634376,1634375,1634374,1634373,1634372,1634371,1634370,1634369,1634368,1634367,1634366,1634365,1634364,1634363,1634362,1634361,1634360,1634359,1634358,1634357,1634356,1634355,1634354,1634353,1634352,1634350,1634349,1634348,1634347,1634346,1634345,1634344,1634343,1634342,1634341,1634340,1634339,1634338,1634337,1634336,1634335,1634334,1634333,1634332,1634331,1634330,1634093,1634092,1634091,1634090,1634089,1634088,1634087,1634086,1634085,1634084,1634083,1634082,1634081,1634080,1634079,1634078,1634077,1634076,1634075,1634074,1634073,1634072,1634071,1634070,1634069,1634068,1634067,1634066,1634065,1634064,1634063,1634062,1634061,1634060,1634059,1634058,1634057,1634056,1634055,1634054,1634053,1634052,1634051,1634050,1634049,1634048,1634047,1634046,1634045,1634044,1634043,1634042,1634041,1634040,1634039,1634038,1634037,1634036,1634034,1634033,1634032,1634031,1634030,1634029,1634028,1634027,1634026,1634025,1634024,1634023,1634022,1634021,1634020,1634019,1634018,1634017,1634016,1634015,1634014,1634013,1634012,1634011,1634010,1634009,1634008,1634007,1634006,1634005,1634004,1634003,1634002,1634001,1634000,1633999,1633998}'::integer[])) AND (listid = 1))
-> Bitmap Index Scan on email_list_subscribers_sub_list_idx (cost=0.00..2050.86 rows=377 width=0) (actual time=1.080..1.080 rows=500 loops=1)
Index Cond: ((subscriberid = ANY ('{1634516,1634515,1634514,1634513,1634512,1634511,1634510,1634509,1634508,1634507,1634506,1634505,1634504,1634503,1634502,1634501,1634500,1634499,1634498,1634497,1634496,1634495,1634494,1634493,1634492,1634491,1634490,1634489,1634488,1634487,1634486,1634485,1634484,1634483,1634482,1634481,1634480,1634479,1634478,1634477,1634476,1634474,1634473,1634472,1634471,1634470,1634469,1634468,1634467,1634466,1634465,1634464,1634463,1634462,1634461,1634460,1634459,1634458,1634457,1634456,1634455,1634454,1634453,1634452,1634451,1634450,1634449,1634448,1634447,1634446,1634445,1634444,1634443,1634442,1634441,1634329,1634328,1634327,1634326,1634325,1634324,1634323,1634322,1634321,1634320,1634319,1634318,1634317,1634316,1634315,1634314,1634313,1634312,1634311,1634310,1634309,1634308,1634307,1634306,1634305,1634304,1634639,1634638,1634637,1634636,1634635,1634634,1634633,1634632,1634631,1634630,1634629,1634628,1634627,1634626,1634625,1634624,1634623,1634622,1634621,1634620,1634619,1634618,1634617,1634616,1634615,1634614,1634613,1634612,1634611,1634610,1634609,1634608,1634607,1634606,1634605,1634604,1634603,1634602,1634601,1634600,1634599,1634598,1634303,1634302,1634301,1634300,1634299,1634298,1634297,1634296,1634295,1634294,1634293,1634292,1634291,1634290,1634289,1634288,1634287,1634286,1634285,1634284,1634283,1634282,1634281,1634280,1634279,1634278,1634277,1634276,1634275,1634274,1634273,1634272,1634271,1634270,1634269,1634268,1634267,1634266,1634265,1634264,1634263,1634262,1634261,1634260,1634259,1634258,1634257,1634256,1634255,1634254,1634253,1634252,1634251,1634250,1634249,1634248,1634247,1634246,1634245,1634244,1634243,1634242,1634241,1634240,1634239,1634238,1634237,1634236,1634235,1634234,1634233,1634232,1634231,1634230,1634229,1634228,1634227,1634226,1634225,1634224,1634223,1634222,1634221,1634220,1634219,1634218,1634217,1634216,1634215,1634214,1634213,1634212,1634211,1634210,1634209,1634208,1634207,1634206,1634204,1634203,1634202,1634201,1634200,1634199,1634198,1634197,1634195,1634194,1634193,1634192,1634191,1634190,1634189,1634188,1634187,1634186,1634185,1634184,1634183,1634182,1634181,1634180,1634179,1634178,1634177,1634176,1634175,1634174,1634119,1634118,1634117,1634116,1634115,1634114,1634113,1634112,1634111,1634110,1634109,1634108,1634107,1634106,1634105,1634104,1634103,1634102,1634101,1634100,1634099,1634098,1634097,1634096,1634095,1634094,1634440,1634439,1634438,1634437,1634435,1634434,1634433,1634432,1634431,1634430,1634429,1634428,1634427,1634426,1634425,1634424,1634423,1634422,1634421,1634420,1634419,1634418,1634417,1634416,1634415,1634414,1634412,1634411,1634410,1634409,1634408,1634407,1634406,1634405,1634404,1634403,1634402,1634401,1634400,1634399,1634398,1634397,1634396,1634395,1634394,1634393,1634392,1634391,1634390,1634389,1634388,1634387,1634386,1634385,1634384,1634383,1634382,1634381,1634380,1634379,1634378,1634377,1634376,1634375,1634374,1634373,1634372,1634371,1634370,1634369,1634368,1634367,1634366,1634365,1634364,1634363,1634362,1634361,1634360,1634359,1634358,1634357,1634356,1634355,1634354,1634353,1634352,1634350,1634349,1634348,1634347,1634346,1634345,1634344,1634343,1634342,1634341,1634340,1634339,1634338,1634337,1634336,1634335,1634334,1634333,1634332,1634331,1634330,1634093,1634092,1634091,1634090,1634089,1634088,1634087,1634086,1634085,1634084,1634083,1634082,1634081,1634080,1634079,1634078,1634077,1634076,1634075,1634074,1634073,1634072,1634071,1634070,1634069,1634068,1634067,1634066,1634065,1634064,1634063,1634062,1634061,1634060,1634059,1634058,1634057,1634056,1634055,1634054,1634053,1634052,1634051,1634050,1634049,1634048,1634047,1634046,1634045,1634044,1634043,1634042,1634041,1634040,1634039,1634038,1634037,1634036,1634034,1634033,1634032,1634031,1634030,1634029,1634028,1634027,1634026,1634025,1634024,1634023,1634022,1634021,1634020,1634019,1634018,1634017,1634016,1634015,1634014,1634013,1634012,1634011,1634010,1634009,1634008,1634007,1634006,1634005,1634004,1634003,1634002,1634001,1634000,1633999,1633998}'::integer[])) AND (listid = 1))
-> Materialize (cost=3.01..3.03 rows=2 width=113) (actual time=0.000..0.001 rows=6 loops=500)
-> Hash Join (cost=1.38..3.00 rows=2 width=113) (actual time=0.028..0.035 rows=6 loops=1)
Hash Cond: (cl.fieldid = c.fieldid)
-> Seq Scan on email_customfield_lists cl (cost=0.00..1.59 rows=6 width=8) (actual time=0.008..0.010 rows=6 loops=1)
Filter: (listid = 1)
-> Hash (cost=1.30..1.30 rows=6 width=105) (actual time=0.015..0.015 rows=6 loops=1)
-> Seq Scan on email_customfields c (cost=0.00..1.30 rows=6 width=105) (actual time=0.004..0.011 rows=6 loops=1)
Filter: (((name)::text = ANY ('{"First Name","Last Clicked Mail","Last Name","Last Open Mail",Mobile,"Postal/Zip Code"}'::text[])) OR (fieldid = ANY ('{2,3}'::integer[])))
-> Index Scan using email_subscribers_data_subscriber_field_idx on email_subscribers_data d (cost=0.00..9.74 rows=2 width=19) (actual time=0.002..0.002 rows=0 loops=3000)
Index Cond: ((d.subscriberid = ls.subscriberid) AND (d.fieldid = c.fieldid))
-> Subquery Scan "*SELECT* 2" (cost=2053.96..11069.47 rows=377 width=110) (actual time=1.078..10.536 rows=2993 loops=1)
-> Nested Loop Left Join (cost=2053.96..11065.70 rows=377 width=110) (actual time=1.077..9.666 rows=2993 loops=1)
Filter: (d.subscriberid IS NULL)
-> Nested Loop (cost=2053.96..3701.37 rows=754 width=110) (actual time=1.073..2.302 rows=3000 loops=1)
-> Bitmap Heap Scan on email_list_subscribers ls (cost=2050.95..3683.28 rows=377 width=8) (actual time=1.048..1.117 rows=500 loops=1)
Recheck Cond: ((subscriberid = ANY ('{1634516,1634515,1634514,1634513,1634512,1634511,1634510,1634509,1634508,1634507,1634506,1634505,1634504,1634503,1634502,1634501,1634500,1634499,1634498,1634497,1634496,1634495,1634494,1634493,1634492,1634491,1634490,1634489,1634488,1634487,1634486,1634485,1634484,1634483,1634482,1634481,1634480,1634479,1634478,1634477,1634476,1634474,1634473,1634472,1634471,1634470,1634469,1634468,1634467,1634466,1634465,1634464,1634463,1634462,1634461,1634460,1634459,1634458,1634457,1634456,1634455,1634454,1634453,1634452,1634451,1634450,1634449,1634448,1634447,1634446,1634445,1634444,1634443,1634442,1634441,1634329,1634328,1634327,1634326,1634325,1634324,1634323,1634322,1634321,1634320,1634319,1634318,1634317,1634316,1634315,1634314,1634313,1634312,1634311,1634310,1634309,1634308,1634307,1634306,1634305,1634304,1634639,1634638,1634637,1634636,1634635,1634634,1634633,1634632,1634631,1634630,1634629,1634628,1634627,1634626,1634625,1634624,1634623,1634622,1634621,1634620,1634619,1634618,1634617,1634616,1634615,1634614,1634613,1634612,1634611,1634610,1634609,1634608,1634607,1634606,1634605,1634604,1634603,1634602,1634601,1634600,1634599,1634598,1634303,1634302,1634301,1634300,1634299,1634298,1634297,1634296,1634295,1634294,1634293,1634292,1634291,1634290,1634289,1634288,1634287,1634286,1634285,1634284,1634283,1634282,1634281,1634280,1634279,1634278,1634277,1634276,1634275,1634274,1634273,1634272,1634271,1634270,1634269,1634268,1634267,1634266,1634265,1634264,1634263,1634262,1634261,1634260,1634259,1634258,1634257,1634256,1634255,1634254,1634253,1634252,1634251,1634250,1634249,1634248,1634247,1634246,1634245,1634244,1634243,1634242,1634241,1634240,1634239,1634238,1634237,1634236,1634235,1634234,1634233,1634232,1634231,1634230,1634229,1634228,1634227,1634226,1634225,1634224,1634223,1634222,1634221,1634220,1634219,1634218,1634217,1634216,1634215,1634214,1634213,1634212,1634211,1634210,1634209,1634208,1634207,1634206,1634204,1634203,1634202,1634201,1634200,1634199,1634198,1634197,1634195,1634194,1634193,1634192,1634191,1634190,1634189,1634188,1634187,1634186,1634185,1634184,1634183,1634182,1634181,1634180,1634179,1634178,1634177,1634176,1634175,1634174,1634119,1634118,1634117,1634116,1634115,1634114,1634113,1634112,1634111,1634110,1634109,1634108,1634107,1634106,1634105,1634104,1634103,1634102,1634101,1634100,1634099,1634098,1634097,1634096,1634095,1634094,1634440,1634439,1634438,1634437,1634435,1634434,1634433,1634432,1634431,1634430,1634429,1634428,1634427,1634426,1634425,1634424,1634423,1634422,1634421,1634420,1634419,1634418,1634417,1634416,1634415,1634414,1634412,1634411,1634410,1634409,1634408,1634407,1634406,1634405,1634404,1634403,1634402,1634401,1634400,1634399,1634398,1634397,1634396,1634395,1634394,1634393,1634392,1634391,1634390,1634389,1634388,1634387,1634386,1634385,1634384,1634383,1634382,1634381,1634380,1634379,1634378,1634377,1634376,1634375,1634374,1634373,1634372,1634371,1634370,1634369,1634368,1634367,1634366,1634365,1634364,1634363,1634362,1634361,1634360,1634359,1634358,1634357,1634356,1634355,1634354,1634353,1634352,1634350,1634349,1634348,1634347,1634346,1634345,1634344,1634343,1634342,1634341,1634340,1634339,1634338,1634337,1634336,1634335,1634334,1634333,1634332,1634331,1634330,1634093,1634092,1634091,1634090,1634089,1634088,1634087,1634086,1634085,1634084,1634083,1634082,1634081,1634080,1634079,1634078,1634077,1634076,1634075,1634074,1634073,1634072,1634071,1634070,1634069,1634068,1634067,1634066,1634065,1634064,1634063,1634062,1634061,1634060,1634059,1634058,1634057,1634056,1634055,1634054,1634053,1634052,1634051,1634050,1634049,1634048,1634047,1634046,1634045,1634044,1634043,1634042,1634041,1634040,1634039,1634038,1634037,1634036,1634034,1634033,1634032,1634031,1634030,1634029,1634028,1634027,1634026,1634025,1634024,1634023,1634022,1634021,1634020,1634019,1634018,1634017,1634016,1634015,1634014,1634013,1634012,1634011,1634010,1634009,1634008,1634007,1634006,1634005,1634004,1634003,1634002,1634001,1634000,1633999,1633998}'::integer[])) AND (listid = 1))
-> Bitmap Index Scan on email_list_subscribers_sub_list_idx (cost=0.00..2050.86 rows=377 width=0) (actual time=1.045..1.045 rows=500 loops=1)
Index Cond: ((subscriberid = ANY ('{1634516,1634515,1634514,1634513,1634512,1634511,1634510,1634509,1634508,1634507,1634506,1634505,1634504,1634503,1634502,1634501,1634500,1634499,1634498,1634497,1634496,1634495,1634494,1634493,1634492,1634491,1634490,1634489,1634488,1634487,1634486,1634485,1634484,1634483,1634482,1634481,1634480,1634479,1634478,1634477,1634476,1634474,1634473,1634472,1634471,1634470,1634469,1634468,1634467,1634466,1634465,1634464,1634463,1634462,1634461,1634460,1634459,1634458,1634457,1634456,1634455,1634454,1634453,1634452,1634451,1634450,1634449,1634448,1634447,1634446,1634445,1634444,1634443,1634442,1634441,1634329,1634328,1634327,1634326,1634325,1634324,1634323,1634322,1634321,1634320,1634319,1634318,1634317,1634316,1634315,1634314,1634313,1634312,1634311,1634310,1634309,1634308,1634307,1634306,1634305,1634304,1634639,1634638,1634637,1634636,1634635,1634634,1634633,1634632,1634631,1634630,1634629,1634628,1634627,1634626,1634625,1634624,1634623,1634622,1634621,1634620,1634619,1634618,1634617,1634616,1634615,1634614,1634613,1634612,1634611,1634610,1634609,1634608,1634607,1634606,1634605,1634604,1634603,1634602,1634601,1634600,1634599,1634598,1634303,1634302,1634301,1634300,1634299,1634298,1634297,1634296,1634295,1634294,1634293,1634292,1634291,1634290,1634289,1634288,1634287,1634286,1634285,1634284,1634283,1634282,1634281,1634280,1634279,1634278,1634277,1634276,1634275,1634274,1634273,1634272,1634271,1634270,1634269,1634268,1634267,1634266,1634265,1634264,1634263,1634262,1634261,1634260,1634259,1634258,1634257,1634256,1634255,1634254,1634253,1634252,1634251,1634250,1634249,1634248,1634247,1634246,1634245,1634244,1634243,1634242,1634241,1634240,1634239,1634238,1634237,1634236,1634235,1634234,1634233,1634232,1634231,1634230,1634229,1634228,1634227,1634226,1634225,1634224,1634223,1634222,1634221,1634220,1634219,1634218,1634217,1634216,1634215,1634214,1634213,1634212,1634211,1634210,1634209,1634208,1634207,1634206,1634204,1634203,1634202,1634201,1634200,1634199,1634198,1634197,1634195,1634194,1634193,1634192,1634191,1634190,1634189,1634188,1634187,1634186,1634185,1634184,1634183,1634182,1634181,1634180,1634179,1634178,1634177,1634176,1634175,1634174,1634119,1634118,1634117,1634116,1634115,1634114,1634113,1634112,1634111,1634110,1634109,1634108,1634107,1634106,1634105,1634104,1634103,1634102,1634101,1634100,1634099,1634098,1634097,1634096,1634095,1634094,1634440,1634439,1634438,1634437,1634435,1634434,1634433,1634432,1634431,1634430,1634429,1634428,1634427,1634426,1634425,1634424,1634423,1634422,1634421,1634420,1634419,1634418,1634417,1634416,1634415,1634414,1634412,1634411,1634410,1634409,1634408,1634407,1634406,1634405,1634404,1634403,1634402,1634401,1634400,1634399,1634398,1634397,1634396,1634395,1634394,1634393,1634392,1634391,1634390,1634389,1634388,1634387,1634386,1634385,1634384,1634383,1634382,1634381,1634380,1634379,1634378,1634377,1634376,1634375,1634374,1634373,1634372,1634371,1634370,1634369,1634368,1634367,1634366,1634365,1634364,1634363,1634362,1634361,1634360,1634359,1634358,1634357,1634356,1634355,1634354,1634353,1634352,1634350,1634349,1634348,1634347,1634346,1634345,1634344,1634343,1634342,1634341,1634340,1634339,1634338,1634337,1634336,1634335,1634334,1634333,1634332,1634331,1634330,1634093,1634092,1634091,1634090,1634089,1634088,1634087,1634086,1634085,1634084,1634083,1634082,1634081,1634080,1634079,1634078,1634077,1634076,1634075,1634074,1634073,1634072,1634071,1634070,1634069,1634068,1634067,1634066,1634065,1634064,1634063,1634062,1634061,1634060,1634059,1634058,1634057,1634056,1634055,1634054,1634053,1634052,1634051,1634050,1634049,1634048,1634047,1634046,1634045,1634044,1634043,1634042,1634041,1634040,1634039,1634038,1634037,1634036,1634034,1634033,1634032,1634031,1634030,1634029,1634028,1634027,1634026,1634025,1634024,1634023,1634022,1634021,1634020,1634019,1634018,1634017,1634016,1634015,1634014,1634013,1634012,1634011,1634010,1634009,1634008,1634007,1634006,1634005,1634004,1634003,1634002,1634001,1634000,1633999,1633998}'::integer[])) AND (listid = 1))
-> Materialize (cost=3.01..3.03 rows=2 width=110) (actual time=0.000..0.001 rows=6 loops=500)
-> Hash Join (cost=1.38..3.00 rows=2 width=110) (actual time=0.022..0.027 rows=6 loops=1)
Hash Cond: (cl.fieldid = c.fieldid)
-> Seq Scan on email_customfield_lists cl (cost=0.00..1.59 rows=6 width=8) (actual time=0.005..0.007 rows=6 loops=1)
Filter: (listid = 1)
-> Hash (cost=1.30..1.30 rows=6 width=106) (actual time=0.012..0.012 rows=6 loops=1)
-> Seq Scan on email_customfields c (cost=0.00..1.30 rows=6 width=106) (actual time=0.003..0.009 rows=6 loops=1)
Filter: (((name)::text = ANY ('{"First Name","Last Clicked Mail","Last Name","Last Open Mail",Mobile,"Postal/Zip Code"}'::text[])) OR (fieldid = ANY ('{2,3}'::integer[])))
-> Index Scan using email_subscribers_data_subscriber_field_idx on email_subscribers_data d (cost=0.00..9.74 rows=2 width=8) (actual time=0.002..0.002 rows=0 loops=3000)
Index Cond: ((ls.subscriberid = d.subscriberid) AND (c.fieldid = d.fieldid))
Total runtime: 339.942 ms
(40 rows)J'ai vérifier les différents index utilisé et tout à l'ai prix en compte.
Mais il y a 2 moments ou il fait un "Bitmap Index Scan" et ces 2 passages sont très long, de plus il y a pluiseurs recheck que je ne comprend pas.
Merci de votre aide précieuse.
Cdlt
Daivd
Hors ligne
Ce qui semble prendre du temps, c'est l'étape UNIQUE, du au 'UNION'.
Pouvez vous :
- Essayer la même requête avec un UNION ALL
- Essayer la même requête, à nouveau avec le UNION, mais sans la colonne data.
Dans les deux cas, pouvez vous me donner les temps d'exécution ?
Marc.
Hors ligne
Ha ouai!!! énorme
Le temps est passé à 20ms avec le union all.
Par contre, la suppression de la colonne data dans la partie select na pas d'effet. 340ms
c'est donc la déduplication des valeurs qui poserait problème? y a t'il un moyen d'optimiser ce traitement ?
Cdlt
David
Hors ligne
Y a t'il 'besoin' de dédupliquer les données ? Pas sur la requête en tout cas, d'après le plan.
Pour la rendre plus rapide, il faut limiter le nombre de champs sur lesquels dédupliquer (pour limiter la quantité de tri).
300ms pour 3000 enregistrements, c'est un chiffre un peu énorme. Il n'y aurait pas un des champs qui serait très gros ? Ou des types particuliers ?
=> Quelle est le type de chacun des champs retournés ?
Marc.
Hors ligne
Voila les types de champs
c.fieldid INTEGER
c.name VarCHAR
c.fieldtype varchar(100)
c.fieldsettings text
d.subscriberid integer
data text
je suis étonné de trouver un varchar sans taille, ainsi que des text field pour les paramétres
Est ce que de passer à des varchar avec des tailles défini pourrait aider ?
Cdlt
David
Hors ligne
le champ fieldsettings a besoin d' une longeur de 9000 char pour passer
Hors ligne
Alors c'est probablement fieldsettings qui pose problème (long à trier).
Pouvez vous tester la requête sans lui, pour voir ? (nous trouverons une solution au problème une fois que nous l'aurons diagnostiqué correctement 
)
Sinon, oui, la taille est optionnelle sur les varchar sous PostgreSQL. Si vous ne précisez pas de taille, ça veut dire qu'il n'y a pas de limite (enfin si, à 1Go).
Passer à une taille définie ne changera (heureusement) rien: les chaînes ne prennent que leur taille réelle. La taille n'est rien d'autre qu'une contrainte.
Marc.
Hors ligne
Pages : 1