Re: duplicate rows in query
От | Alban Hertroys |
---|---|
Тема | Re: duplicate rows in query |
Дата | |
Msg-id | 2C9FA7DF-6EDC-485E-9D15-88D0C0A8ADA7@solfertje.student.utwente.nl обсуждение исходный текст |
Ответ на | duplicate rows in query ("Mark Watson" <mark.watson@jurisconcept.qc.ca>) |
Список | pgsql-general |
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@postgresql.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!
В списке pgsql-general по дате отправления: