Bonjour à tous,
J'ai une table tbl_clients qui contient environ 600 000 lignes (nombre qui va probablement doubler d'ici 1 an)
Voici les infos complètes :
La colonne id_tbl_cat_poi est un entier qui est lié à la table tbl_cat_poi suivante (environ 80 lignes, bougera peu)
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147 -- Table: tbl_clients -- DROP TABLE tbl_clients; CREATE TABLE tbl_clients ( id_tbl_clients integer NOT NULL DEFAULT nextval(('seq_clients'::text)::regclass), ref_client character varying(8), title character varying(8), f_name character varying(30), l_name character varying(30), company character varying(50), address_1 character varying(50), address_2 character varying(50), address_3 character varying(50), postcode character varying(10), town character varying(30), country character varying(30), client_geolocal point NOT NULL DEFAULT '(0,0)'::point, siret character varying(15), cr_rating character varying(5), restriction character varying(15), access_id integer, contact_details character varying(110), date_created timestamp without time zone NOT NULL DEFAULT (('now'::text)::timestamp(6) with time zone)::timestamp(0) without time zone, created_by name NOT NULL DEFAULT "current_user"(), credit_terms smallint DEFAULT 0, the_geom geometry, usr_login character varying, usr_password character varying, usr_auth character varying, geo_adresse character varying, geo_codepostal integer, geo_ville character varying, geo_note character varying, geo_lat double precision, geo_lng double precision, parent_id integer, poi boolean, geo_status character varying, addr_numrue character varying, poi_name character varying(100), id_tbl_cat_poi integer, osm_id integer, usr_webcode character varying(20), statusnet_id bigint, addr_bister text, draft text, geo_numrue integer, geo_bister text, ad_properties bit(8) NOT NULL DEFAULT B'11111111'::"bit", message_properties bit(4) NOT NULL DEFAULT B'1111'::"bit", poi_properties bit(4) NOT NULL DEFAULT B'0000'::"bit", last_activity timestamp without time zone DEFAULT now(), CONSTRAINT enforce_dims_the_geom CHECK (ndims(the_geom) = 2), CONSTRAINT enforce_geotype_the_geom CHECK (geometrytype(the_geom) = 'POINT'::text OR the_geom IS NULL), CONSTRAINT enforce_srid_the_geom CHECK (srid(the_geom) = 4326) ) WITH ( OIDS=FALSE ); ALTER TABLE tbl_clients OWNER TO operators; COMMENT ON TABLE tbl_clients IS 'sdfsdfsdf'; -- Index: inx_clients_company -- DROP INDEX inx_clients_company; CREATE INDEX inx_clients_company ON tbl_clients USING btree (l_name); -- Index: inx_clients_idtblcatpoi -- DROP INDEX inx_clients_idtblcatpoi; CREATE INDEX inx_clients_idtblcatpoi ON tbl_clients USING btree (id_tbl_cat_poi); -- Index: inx_clients_l_name -- DROP INDEX inx_clients_l_name; CREATE INDEX inx_clients_l_name ON tbl_clients USING btree (l_name); -- Index: inx_clients_osm_id -- DROP INDEX inx_clients_osm_id; CREATE INDEX inx_clients_osm_id ON tbl_clients USING btree (osm_id); -- Index: inx_clients_ref_client -- DROP INDEX inx_clients_ref_client; CREATE UNIQUE INDEX inx_clients_ref_client ON tbl_clients USING btree (ref_client); -- Index: inx_clients_restriction -- DROP INDEX inx_clients_restriction; CREATE INDEX inx_clients_restriction ON tbl_clients USING btree (restriction); -- Index: inx_clients_the_geom -- DROP INDEX inx_clients_the_geom; CREATE INDEX inx_clients_the_geom ON tbl_clients USING gist (the_geom); -- Index: tbl_clients_statusnet_id_idx -- DROP INDEX tbl_clients_statusnet_id_idx; CREATE INDEX tbl_clients_statusnet_id_idx ON tbl_clients USING btree (statusnet_id); -- Trigger: update_insert_clients on tbl_clients -- DROP TRIGGER update_insert_clients ON tbl_clients; CREATE TRIGGER update_insert_clients BEFORE INSERT OR UPDATE ON tbl_clients FOR EACH ROW EXECUTE PROCEDURE trigger_set_client_properties();
La requête suivante , qui récupère les informations de la catégorie et le nombre de client par catégorie, est gourmande en ressource
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19 CREATE TABLE tbl_cat_poi ( id_tbl_cat_poi serial NOT NULL, parent_id smallint NOT NULL DEFAULT 0, item_level smallint NOT NULL DEFAULT 0, item_text character varying(50) NOT NULL DEFAULT ''::character varying, ad_properties bit(8) NOT NULL DEFAULT B'00011111'::"bit", message_properties bit(4) NOT NULL DEFAULT B'0001'::"bit", poi_properties bit(4) NOT NULL DEFAULT B'1111'::"bit", sort_order smallint NOT NULL DEFAULT 0, nb_to_display integer, min_distance integer, max_distance integer, CONSTRAINT tbl_cat_poi_pkey PRIMARY KEY (id_tbl_cat_poi) ) WITH ( OIDS=FALSE ); ALTER TABLE tbl_cat_poi OWNER TO operators;
Voici ce que donne l'explain :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10 SELECT cat.item_text, cat.id_tbl_cat_poi, cat.parent_id, count(id_tbl_clients) as nombre FROM tbl_clients tc INNER JOIN tbl_cat_poi cat ON tc.id_tbl_cat_poi = cat.id_tbl_cat_poi WHERE 2>1 AND tc.id_tbl_clients != 699317 AND DISTANCE( TRANSFORM(tc.the_geom, 27582) , TRANSFORM(ST_SetSRID(ST_MakePoint(3.8853803239425,43.598034187644),4326), 27582) ) <= 5000 AND ST_DWithin(tc.the_geom, ST_SetSRID(ST_MakePoint(3.8853803239425,43.598034187644),4326) ,0.4) AND tc.poi_properties & b'0000' = b'0000' GROUP BY cat.item_text, cat.id_tbl_cat_poi, cat.parent_id ORDER BY cat.item_text ;
Lorsque je lance cette requête, elle prend environ 2 secondes avec un work_mem "optimisé" pour cette requête.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11 GroupAggregate (cost=243345.49..243345.52 rows=1 width=27) -> Sort (cost=243345.49..243345.49 rows=1 width=27) Sort Key: cat.item_text, cat.id_tbl_cat_poi, cat.parent_id -> Nested Loop (cost=12608.71..243345.48 rows=1 width=27) Join Filter: (tc.id_tbl_cat_poi = cat.id_tbl_cat_poi) -> Bitmap Heap Scan on tbl_clients tc (cost=12608.71..243342.81 rows=1 width=8) Recheck Cond: (the_geom && '0103000020E61000000100000005000000AFA34B140FE20B40E0B52B2F59994540AFA34B140FE20B40461C9295BFFF45400B0559BD3A241140461C9295BFFF45400B0559BD3A241140E0B52B2F59994540AFA34B140FE20B40E0B52B2F59994540'::geometry) Filter: ((id_tbl_clients <> 699317) AND ('0101000020E6100000E2D67E4742150F4013E95E628CCC4540'::geometry && st_expand(the_geom, 0.4::double precision)) AND ((poi_properties & B'0000'::"bit") = B'0000'::"bit") AND _st_dwithin(the_geom, '0101000020E6100000E2D67E4742150F4013E95E628CCC4540'::geometry, 0.4::double precision) AND (distance(transform(the_geom, 27582), '0101000020BE6B00003558DB6BD8212641639D4E1DFD273C41'::geometry) <= 5000::double precision)) -> Bitmap Index Scan on inx_clients_the_geom (cost=0.00..12608.71 rows=338170 width=0) Index Cond: (the_geom && '0103000020E61000000100000005000000AFA34B140FE20B40E0B52B2F59994540AFA34B140FE20B40461C9295BFFF45400B0559BD3A241140461C9295BFFF45400B0559BD3A241140E0B52B2F59994540AFA34B140FE20B40E0B52B2F59994540'::geometry) -> Seq Scan on tbl_cat_poi cat (cost=0.00..1.74 rows=74 width=23)
J'ai essayé d'optimiser via le work_meme : j'ai fait un test en ajoutant un
SET work_mem TO '10MB';
avant de lancer la requête. Voici les résultats en secondes : colonne 1 = valeur du work_mem, colonne 2 = millisecondes)
J'ai donc choisi la valeur 10MB.
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
12 1 6124 2 3311 3 2563 5 2517 6 2490 10 2309 20 2320 50 2300 100 2300 200 2295 400 2297
shared_buffer est à 24MB
Je ne sais pas bien comment optimiser cette requête. Elle est importante car elle me sert à créer un arbre des catégories en PHP pour un site internet. Elle est donc utilisée souvent.
J'ai "pensé" au work_mem et au shared_buffer à force de parcourir les messages liés à l'optimisation sur le forum, mais je ne suis pas expert.
Sinon j'ai aussi fait un VACUUM, un ANALYSE, et même un REINDEX TABLE tbl_clients.
En espérant vous avoir donné les informations nécessaires pour que vous puissiez m'aider.
Kimaidou
Partager