Vous n'êtes pas identifié(e).
Bonjour,
j'essaie d'enlever des doublons sur une table de 5 000 000 d'enregistrements avec cette requete
delete from majic3_2009.batiart00 where doublongid not in (select first(doublongid) from majic3_2009.batiart00
group by ccodep,ccodir,ccocom,invar,cenr,ccopre,ccosec,dnupla,dnubat,descr,dniv,dpor,ccoriv,ccovoi,dnvoiri,dindic,ccocif,dvoilib,cleinvar,locinc,codpar,id_insee,invar2)
impossible de la finir 2 jours que cela dure
donc j'ai fait un explain analyse pour 10 enregistrements rien que sur le first
explain analyse
select first(doublongid) from majic3_2009.batiart00
group by ccodep,ccodir,ccocom,invar,cenr,ccopre,ccosec,dnupla,dnubat,descr,dniv,dpor,ccoriv,ccovoi,dnvoiri,dindic,ccocif,dvoilib,cleinvar,locinc,codpar,id_insee
,invar2
limit 10
"Limit (cost=1166123.80..1166124.81 rows=10 width=154) (actual time=125320.679..125320.763 rows=10 loops=1)"
" -> GroupAggregate (cost=1166123.80..1538344.07 rows=3683517 width=154) (actual time=125320.678..125320.758 rows=10 loops=1)"
" -> Sort (cost=1166123.80..1179170.85 rows=5218821 width=154) (actual time=125320.550..125320.565 rows=11 loops=1)"
" Sort Key: ccodep, ccodir, ccocom, invar, cenr, ccopre, ccosec, dnupla, dnubat, descr, dniv, dpor, ccoriv, ccovoi, dnvoiri, dindic, ccocif, dvoilib, cleinvar, locinc, codpar, id_insee, invar2"
" Sort Method: external merge Disk: 841720kB"
" -> Seq Scan on batiart00 (cost=0.00..173556.21 rows=5218821 width=154) (actual time=0.008..2217.042 rows=5218821 loops=1)"
"Total runtime: 127580.796 ms"
que faut t'il faire optimiser le postgres.conf , changer l'ecriture de la requete ??
merci de votre aide
Hors ligne
Le gros du travail a l'air de se faire sur le tri. Un index sur les colonnes ccodep, ccodir, ccocom, invar, cenr, ccopre, ccosec, dnupla, dnubat, descr, dniv, dpor, ccoriv, ccovoi, dnvoiri, dindic, ccocif, dvoilib, cleinvar, locinc, codpar, id_insee, invar2 pourrait aider à éviter le tri.
Guillaume.
Hors ligne
un index multicolonne est ce bien cela merci
http://docs.postgresqlfr.org/8.3/indexe … olumn.html
Hors ligne
merci c 'est plus rapide j'ai une autre table ou il y plus de 32 champs , comment faire creer 2 index multiples ?? merci
la voici ci dessous
voici ma seconde table ou il y a des doublons
CREATE TABLE locaux
(
id_invar text,
codpar text,
ccodep character varying(2),
ccodir character varying(1),
ccocom character varying(3),
invar character varying(10),
cenr character varying(2),
ccopre character varying(3),
ccosec character varying(2),
dnupla character varying(4),
dnubat character varying(2),
descr character varying(2),
dniv character varying(2),
dpor character varying(5),
ccoriv character varying(4),
ccovoi character varying(5),
dnvoiri character varying(4),
dindic character varying(1),
ccocif character varying(4),
dvoilib character varying(30),
cleinvar character varying(1),
locinc character varying(1),
codparc character varying(14),
id_insee character varying(5),
codpro text,
gpdl character varying(1),
dsrpar character varying(1),
dnupro character varying(6),
jdatat character varying(8),
dnufnl character varying(6),
ccoeva character varying(1),
ccitlv character varying(1),
dteloc character varying(1),
gtauom character varying(2),
dcomrd character varying(3),
ccoplc character varying(1),
cconlc character varying(2),
dvltrt character varying(9),
cc48lc character varying(2),
dloy48a character varying(9),
top48a character varying(1),
dnatlc character varying(1),
dnupas character varying(8),
gnexcf character varying(2),
dtaucf character varying(3),
cchpr character varying(1),
jannat character varying(4),
dnbniv character varying(2),
hlmsem character varying(1),
postel character varying(1),
dnatcg character varying(2),
jdatcgl character varying(8),
dnutbx character varying(6),
dvltla character varying(9),
janloc character varying(4),
ccsloc character varying(2),
fburx character varying(1),
gimtom character varying(1),
cbtabt character varying(2),
jdtabt character varying(4),
jrtabt character varying(4),
jacloc character varying(4),
pphsd bigint,
pphad bigint,
depdist bigint,
pnh bigint,
pnhsd bigint,
dnbppr numeric,
dnbsam numeric,
dnbcha numeric,
dnbcu8 numeric,
dnbcu9 numeric,
dnbsea numeric,
dnbann numeric,
dnbpdc numeric,
dsupdc numeric,
dsueic bigint,
vsurzt numeric,
dsudep numeric,
typologt integer,
invar2 character varying(20)
Dernière modification par mich30 (04/02/2013 10:44:47)
Hors ligne
Deux fois "CREATE INDEX" ?
Guillaume.
Hors ligne
je vais creer 2 index combinées merci Gleu
Hors ligne
pour les doublons il faut faire
create table new_batiart as
(
select a.* from batiart a
union
select b.* from batiart b
);
et c'est fini cela va trés vite
Merci Gleu
Hors ligne
