Forums PostgreSQL.fr

dbigand

Membre

Besoin d'aide

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

Marc Cousin

Membre

Re : Besoin d'aide

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.

dbigand

Membre

Re : Besoin d'aide

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

Marc Cousin

Membre

Re : Besoin d'aide

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.

dbigand

Membre

Re : Besoin d'aide

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

dbigand

Membre

Re : Besoin d'aide

le champ fieldsettings a besoin d' une longeur de 9000 char pour passer

Marc Cousin

Membre

Re : Besoin d'aide

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.