On May 22, 2009, at 9:41 PM, Mark Watson wrote:
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.
...
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)"
There you go, there are two matching rows in f_quote_client and since
you join on that you get two rows in your result set.
" -> 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. I’ll be happy to provide any additional information. I’m
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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.
!DSPAM:737,4a17c3ee10091470919307!
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
