Re: duplicate rows in query

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(at)postgresql(dot)org)
> 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!