Messages de dbigand

dbigand · Optimisation

Est ce que ça marche avec un like ou c'est pareil il n'utilise pas les index ? Et est ce qu'avec un tsvector ou gin on pourrais faire quelques optimisations ?

dbigand · Optimisation

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

dbigand · Optimisation

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 · Optimisation

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

dbigand · Optimisation

bon une autre question ?

explain analyse SELECT subscriberid, listid FROM email_list_subscribers WHERE listid IN (2) AND emailaddress ILIKE 'email@server.fr' AND unsubscribed=0 AND bounced=0;
                                                                             QUERY PLAN                                                                             
--------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using email_list_subscribers_listid_idx on email_list_subscribers  (cost=0.00..28338.91 rows=1 width=8) (actual time=1074.862..1074.862 rows=0 loops=1)
   Index Cond: (listid = 2)
   Filter: (((emailaddress)::text ~~* 'email@server.fr'::text) AND (unsubscribed = 0) AND (bounced = 0))
 Total runtime: 1074.8

comme indiqué ci-dessus on voie qu'il passe par un idex qui contient que l'id de list

mais pourtant j'ai créé un index

 CREATE INDEX opti_2_test ON email_list_subscribers USING btree (listid, lower(emailaddress), bounced, unsubscribed)  TABLESPACE "tbs_index" WHERE unsubscribed = 0 AND bounced = 0  ;

alors pourquoi il n'utilise pas cette index???

merci pour vos retours

cdlt

david

dbigand · Optimisation

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

dbigand · Offres

Bonjour,

Nous sommes à la recherche d'un développeur web, maitrisant php5 et postgresql 8.3.

La maitrise C++ serait un plus.

Nous recherchons une personne motivée, efficace et professionnel.

La motivation et l'expérience sont des atouts plus important que les diplomes pour nous.

Nous sommes une société qui existe depuis 4 ans. Notre logiciel est un plateforme de tracking et d'administration sous forme d'adserver pour mesurer le traffic et les actions des internautes à travers les opérations que l'on met en place chez nos partenaires.

Merci de me répondre par le formulaire de contact.

Cdlt

David Bigand

#1 Re : Optimisation » que fait l'optimiseur de requete » 22/10/2010 19:05:58

#2 Re : Optimisation » Besoin d'aide » 22/10/2010 18:37:36

#3 Re : Optimisation » Besoin d'aide » 22/10/2010 18:28:48

#4 Re : Optimisation » Besoin d'aide » 22/10/2010 18:07:01

#5 Optimisation » que fait l'optimiseur de requete » 22/10/2010 17:29:55

#6 Optimisation » Besoin d'aide » 22/10/2010 16:51:07

#7 Offres » Recherche Dev. PHP5 et Postgresql, Savoie, Chambéry » 21/11/2009 13:55:19

Pied de page des forums

PostgreSQL.fr

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux