From: | "ir(dot) F(dot)T(dot)M(dot) van Vugt bc(dot)" <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-02 23:51:03 |
Message-ID: | 200212030051.03635.ftm.van.vugt@foxi.nl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-performance |
(Should probably be in [SQL] by now....)
I've changed my table declarations to agree on the datatypes and only one
simular problem with an update-query doesn't seem to be solved.
(see plan below)
* the concatenation in the lbar select can't be avoided, it's just the way the
data is => this does result in a resulting type 'text', AFAIK
* the aux_address.old_id is also of type 'text'
Still, the planner does a nested loop here against large costs... ;(
Any hints on this (last) one....?
TIA,
Frank.
trial=# explain update address set region_id = lbar.region_id from
(select debtor_id || '-' || address_seqnr as id, region_id from
list_base_regions) as lbar, aux_address aa
where lbar.id = aa.old_id and address.id = aa.id;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------
Merge Join (cost=1.07..65.50 rows=3 width=253)
Merge Cond: ("outer".id = "inner".id)
-> 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)
-> 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..71.80 rows=3980
width=12)
-> 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)
From | Date | Subject | |
---|---|---|---|
Next Message | Ron Johnson | 2002-12-03 03:56:04 | Re: |
Previous Message | Hannu Krosing | 2002-12-02 21:18:14 | Re: |