Hello all,
I have a perplexing problem which I cannot figure out. I have a somewhat
complex query that is returning two identical rows, where only one row
exists in the table. If I run a simpler query, I receive the one row as
desired. This is on a windows vista development machine with Postgres 8.3.7
as localhost.
Simple query:
select * from f_dossier where dono = 'NT003-011'
Query results:
"NT003";"NT003-011";"Accord de confidentialité";"";"S.E.C. Repro inc.";10;
Explain analyze:
"Index Scan using dono_idx on f_dossier (cost=0.00..8.27 rows=1 width=622)
(actual time=0.049..0.051 rows=1 loops=1)"
" Index Cond: ((dono)::text = 'NT003-011'::text)"
"Total runtime: 0.189 ms"
Somewhat complex query:
SELECT do_sequence::text AS Id, DOCLNO
,CASE WHEN do_nom2 = '' OR do_nom2 IS NULL THEN cl_nom2 ELSE do_nom2 END AS
DO_NOM2
,DONO,DOCODE,DO_SEQUENCE,DO_PADVE,DO_PADVE_MAJ,DO_OUVERTURE,DO_FERMETURE_DOS
,DO_NO_FERMETURE,DO_NOM_ASSURE,DO_ADRESSE_EVENEMENT,DO_CATEG,DO_QUOTE_CLIENT
,DO_AVOC1
,DO_CODE_INFO,DO_TYPE_RECLAMATION,DO_ETUDE_REFER,DO_NOTE,DO_SITE,DO_TYPE_FAC
T,DO_NOCOU
,DO_DATE_EVENEMENT,DO_MONT_RECOURS,DO_DERNI_ACT,DO_REF_CLIENT,DO_FERMETURE_P
ROV
,DO_USER_CHAR1, coalesce(Code1,'')::text AS Code1, coalesce(Code2,'')::text
AS Code2
, coalesce(Code3,'')::text AS Code3, coalesce(Code4,'')::text AS Code4
, Associe, Nom, translate(do_padve,E'\x0d',E'\x20') AS Objet--, Site
, ''::text AS MandatExt
, '##' || coalesce(Nom,'') ||'##' || coalesce(DONO,'') || '##' || CASE WHEN
do_nom2 = '' OR do_nom2 IS NULL THEN cl_nom2
ELSE do_nom2 END || '####' || coalesce(Code1,'') || '##' ||
coalesce(Code2,'') || '##' ||
coalesce(Code3,'') || '##' || coalesce(Code4,'') || '##' ||
coalesce(DO_NOM_ASSURE,'') || '##' ||
coalesce(DO_ADRESSE_EVENEMENT,'') || '##' || coalesce(DO_NOTE,'') ||
'##' ||
coalesce(Site,'') || '##' || coalesce(DO_PADVE,'') || '##' ||
coalesce(DO_NOCOU,'') || '##' ||
coalesce(DO_REF_CLIENT,'') AS Recherche
FROM f_dossier
LEFT JOIN (SELECT co_code, co_description as code1 FROM f_code_cour) AS
codecour ON do_categ=co_code
LEFT JOIN (SELECT qc_description as code2 , qc_code FROM f_quote_client) as
quoteclient ON do_quote_client=qc_code
LEFT JOIN (SELECT av_code, av_nom || ', ' || av_prenom AS Associe FROM
f_avocat) AS avocat ON do_avoc1=av_code
LEFT JOIN (SELECT cl_no, cl_nom2, cl_ville AS Ville, cl_code_postal AS
CodePostal, cl_naissance
, CASE WHEN cl_sexe=1 THEN 'M' WHEN cl_sexe=2 THEN 'F' ELSE 'N/A'
END AS sexe
, cl_code3, cl_code4, cl_code6, cl_internet AS Courriel1,
cl_internet2 AS Courriel2
,CASE WHEN cl_prenom='' OR cl_prenom IS NULL THEN cl_nom
ELSE cl_nom || ', ' || cl_prenom END AS Nom FROM f_client)
AS client1 ON doclno=cl_no
--LEFT JOIN (SELECT si_nom AS site, si_id FROM f_site) AS site ON
do_site=si_id
LEFT JOIN (SELECT qc_code as qccode3, qc_description as code3 from
f_quote_client) as tempcode3 on do_code_info = qccode3
LEFT JOIN (SELECT qc_code as qccode4, qc_description as code4 from
f_quote_client) as tempcode4 on do_type_reclamation = qccode4
where dono = 'NT003-011'
order by dono;
Query results:
"10";"NT003";"Louis Bonneville";"NT003-011";
"10";"NT003";"Louis Bonneville";"NT003-011";
Explain analyze:
"Nested Loop Left Join (cost=0.00..256.51 rows=1 width=307) (actual
time=0.470..13.167 rows=2 loops=1)"
" Join Filter: ((f_dossier.do_quote_client)::text =
(public.f_quote_client.qc_code)::text)"
" -> Nested Loop Left Join (cost=0.00..255.38 rows=1 width=305) (actual
time=0.386..13.059 rows=1 loops=1)"
" Join Filter: ((f_dossier.do_categ)::text =
(f_code_cour.co_code)::text)"
" -> Nested Loop Left Join (cost=0.00..254.13 rows=1 width=296)
(actual time=0.333..13.002 rows=1 loops=1)"
" Join Filter: ((f_dossier.do_avoc1)::text =
(f_avocat.av_code)::text)"
" -> Nested Loop Left Join (cost=0.00..250.05 rows=1
width=281) (actual time=0.306..12.796 rows=1 loops=1)"
" Join Filter: ((f_dossier.doclno)::text =
(f_client.cl_no)::text)"
" -> Nested Loop Left Join (cost=0.00..22.61 rows=1
width=206) (actual time=0.149..0.164 rows=1 loops=1)"
" Join Filter: ((f_dossier.do_code_info)::text =
(public.f_quote_client.qc_code)::text)"
" -> Nested Loop Left Join (cost=0.00..21.56
rows=1 width=204) (actual time=0.124..0.135 rows=1 loops=1)"
" Join Filter:
((f_dossier.do_type_reclamation)::text =
(public.f_quote_client.qc_code)::text)"
" -> Nested Loop Left Join
(cost=0.00..20.52 rows=1 width=202) (actual time=0.092..0.100 rows=1
loops=1)"
" Join Filter:
((f_dossier.do_site)::text = (f_site.si_id)::text)"
" -> Index Scan using dono_idx on
f_dossier (cost=0.00..8.27 rows=1 width=134) (actual time=0.063..0.069
rows=1 loops=1)"
" Index Cond: ((dono)::text =
'NT003-011'::text)"
" -> Seq Scan on f_site
(cost=0.00..11.00 rows=100 width=74) (actual time=0.003..0.003 rows=0
loops=1)"
" -> Seq Scan on f_quote_client
(cost=0.00..1.02 rows=2 width=4) (actual time=0.006..0.010 rows=2 loops=1)"
" -> Seq Scan on f_quote_client (cost=0.00..1.02
rows=2 width=4) (actual time=0.003..0.007 rows=2 loops=1)"
" -> Seq Scan on f_client (cost=0.00..175.22 rows=2321
width=61) (actual time=0.027..9.045 rows=2321 loops=1)"
" -> Seq Scan on f_avocat (cost=0.00..3.48 rows=48 width=18)
(actual time=0.008..0.096 rows=48 loops=1)"
" -> Seq Scan on f_code_cour (cost=0.00..1.11 rows=11 width=11)
(actual time=0.006..0.023 rows=11 loops=1)"
" -> Seq Scan on f_quote_client (cost=0.00..1.02 rows=2 width=4) (actual
time=0.003..0.006 rows=2 loops=1)"
"Total runtime: 13.738 ms"
I have rebuilt all indexes to no avail and would love to know how to solve
this. Ill be happy to provide any additional information. Im currently on
the digest version and also will be unavailable until Tuesday, so please do
not consider this an emergency.
Thanks for your time,
Mark Watson
