From: | "Frank van Vugt" <ftm(dot)van(dot)vugt(at)foxi(dot)nl> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
Cc: | Postgresql performance <pgsql-performance(at)postgresql(dot)org> |
Subject: | Re: v7.2.3 versus v7.3 -> huge performance penalty for JOIN with UNION |
Date: | 2002-12-03 09:38:10 |
Message-ID: | 200212031038.10860.ftm.van.vugt@foxi.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
> > Any hints on this (last) one....?
> > -> Nested Loop (cost=0.00..643707.03 rows=3980 width=28)
> > Join Filter: (((("inner".debtor_id)::text || '-'::text) ||
> > ("inner".address_seqnr)::text) = "outer".old_id)
>
> Looks to me like debtor_id and address_seqnr are not text type, but are
> being compared to things that are text.
They were coerced, yes, but changing those original types helps only so much:
* lbar.debtor_id is of type text
* lbar.address_seqnr is of type text
* aa.old_id is of type text
trial=# explain update address set region_id = lbar.region_id from
(select debtor_id || '-' || address_seqnr as f_id, region_id from
list_base_regions) as lbar, aux_address aa
where lbar.f_id = aa.old_id and address.id = aa.id;
Since the left side of the join clause is composed out of three concatenated
text-parts resulting in one single piece of type text, I'd expect the planner
to avoid the nested loop. Still:
QUERY PLAN
--------------------------------------------------------------------------------------------------------
Merge Join (cost=1.07..16.07 rows=1 width=309)
Merge Cond: ("outer".id = "inner".id)
-> Nested Loop (cost=0.00..149669.38 rows=1000 width=84)
Join Filter: ((("inner".debitor_id || '-'::text) ||
"inner".address_seqnr) = "outer".old_id)
-> Index Scan using aux_address_idx2 on aux_address aa
(cost=0.00..81.88 rows=3989 width=16)
-> Seq Scan on list_base_regions (cost=0.00..20.00 rows=1000
width=68)
-> Sort (cost=1.07..1.08 rows=3 width=225)
Sort Key: address.id
-> Seq Scan on address (cost=0.00..1.05 rows=3 width=225)
Filter: ((id = 1) IS NOT TRUE)
(10 rows)
> Hard to tell exactly what's going on though
Does this help?
NB: it seems the data types part of the manual doesn't enlighten me on this
subject, any suggestions where to find more input?
Regards,
Frank.
From | Date | Subject | |
---|---|---|---|
Next Message | Andrew Sullivan | 2002-12-03 12:34:04 | Re: Is there any limitations |
Previous Message | Tom Lane | 2002-12-03 05:59:44 | Re: v7.2.3 versus v7.3 -> huge performance penalty for JOIN with UNION |