Re: Planner regression in 8.0.x ?

I don't have the plan for the original query under 7.4, but you will
note I've posted a work-around for 8.0.4 that runs in a fraction of a
second on 8.0.4, and here's the plan for that:

Sort (cost=2393.24..2393.25 rows=2 width=114)
Sort Key: "_Pending".receipt_date,
"substring"(("_Pending".callsign)::text, '[0-9]'::text),
"_Pending".callsign, "_Pending".uls_file_num, "_Pending".seq_num
-> Nested Loop (cost=0.00..2393.23 rows=2 width=114)
Join Filter: (("outer".prediction)::text ~~
("inner".prediction)::text)
-> Seq Scan on "_Pending" (cost=0.00..2266.61 rows=10 width=112)
Filter: ((((((vanity_type)::text || ' - '::text) ||
(COALESCE((subplan), '???'::character
varying))::text))::bpchar)::character(1) = 'A'::bpchar)
SubPlan
-> Limit (cost=0.00..1.07 rows=1 width=19)
-> Seq Scan on "_VanityType" (cost=0.00..1.07
rows=1 width=19)
Filter: (vanity_id = $8)
-> Seq Scan on "_Prediction" (cost=0.00..1.21 rows=21 width=18)
SubPlan
-> Index Scan using "_HD_pkey" on "_HD" (cost=0.00..3.01
rows=1 width=6)
Index Cond: (unique_system_identifier = $7)
-> Limit (cost=0.00..1.01 rows=1 width=0)
-> Seq Scan on "_ReservedCall" (cost=0.00..1.01
rows=1 width=0)
Filter: ($2 ~ (pattern)::text)
-> Subquery Scan archivejb (cost=22.04..22.20 rows=2 width=0)
-> Unique (cost=22.04..22.18 rows=2 width=229)
-> Sort (cost=22.04..22.04 rows=2 width=229)
Sort Key: callsign, fcc_reg_num,
licensee_id, prev_callsign, trustee_callsign, applicant_type,
operator_class, prev_class, radio_service, license_status, geo_region,
grant_date, effective_date, cancel_date, expire_date, last_action_date,
entity_name, first_name, middle_init, last_name, name_suffix, address,
po_box, city, state, zip9, sys_id
-> Append (cost=0.00..22.03 rows=2 width=229)
-> Subquery Scan "*SELECT* 1"
(cost=0.00..12.12 rows=1 width=229)
-> Nested Loop
(cost=0.00..12.11 rows=1 width=229)
-> Nested Loop
(cost=0.00..9.04 rows=1 width=107)
-> Index Scan
using "_HD_callsign" on "_HD" (cost=0.00..6.01 rows=1 width=49)
Index Cond:
($2 = callsign)
Filter:
(grant_date < $5)
-> Index Scan
using "_AM_pkey" on "_AM" (cost=0.00..3.02 rows=1 width=58)
Index Cond:
("_AM".unique_system_identifier = "outer".unique_system_identifier)
Filter: ($2 =
callsign)
-> Index Scan using
"_EN_pkey" on "_EN" (cost=0.00..3.02 rows=1 width=158)
Index Cond:
("_EN".unique_system_identifier = "outer".unique_system_identifier)
Filter: ((callsign
= $2) AND (licensee_id = $6))
-> Subquery Scan "*SELECT* 2"
(cost=0.00..9.91 rows=1 width=186)
-> Index Scan using
"_Lic_pkey" on "_Lic" (cost=0.00..9.90 rows=1 width=186)
Index Cond: (callsign = $2)
Filter: ((licensee_id =
$6) AND ((COALESCE((grant_date)::timestamp without time zone,
(expire_date - '10 years'::interval)))::date < $5))
-> Subquery Scan archivejb (cost=18.21..18.38 rows=1 width=0)
Filter: (license_status = 'A'::bpchar)
-> Unique (cost=18.21..18.35 rows=2 width=229)
-> Sort (cost=18.21..18.22 rows=2 width=229)
Sort Key: callsign, fcc_reg_num,
licensee_id, prev_callsign, trustee_callsign, applicant_type,
operator_class, prev_class, radio_service, license_status, geo_region,
grant_date, effective_date, cancel_date, expire_date, last_action_date,
entity_name, first_name, middle_init, last_name, name_suffix, address,
po_box, city, state, zip9, sys_id
-> Append (cost=0.00..18.20 rows=2 width=229)
-> Subquery Scan "*SELECT* 1"
(cost=0.00..12.12 rows=1 width=229)
-> Nested Loop
(cost=0.00..12.11 rows=1 width=229)
-> Nested Loop
(cost=0.00..9.04 rows=1 width=107)
-> Index Scan
using "_HD_callsign" on "_HD" (cost=0.00..6.01 rows=1 width=49)
Index Cond:
($4 = callsign)
Filter:
(grant_date < $5)
-> Index Scan
using "_AM_pkey" on "_AM" (cost=0.00..3.02 rows=1 width=58)
Index Cond:
("_AM".unique_system_identifier = "outer".unique_system_identifier)
Filter:
((previous_callsign = $2) AND ($4 = callsign))
-> Index Scan using
"_EN_pkey" on "_EN" (cost=0.00..3.02 rows=1 width=158)
Index Cond:
("_EN".unique_system_identifier = "outer".unique_system_identifier)
Filter: (callsign = $4)
-> Subquery Scan "*SELECT* 2"
(cost=0.00..6.09 rows=1 width=186)
-> Index Scan using
"_Lic_pcall" on "_Lic" (cost=0.00..6.08 rows=1 width=186)
Index Cond:
(prev_callsign = $2)
Filter: ((callsign = $4)
AND ((COALESCE((grant_date)::timestamp without time zone, (expire_date -
'10 years'::interval)))::date < $5))
-> Index Scan using "_HD_pkey" on "_HD" (cost=0.00..5.91
rows=1 width=8)
Index Cond: (unique_system_identifier = $3)
-> Seq Scan on "_GeoRestrict" (cost=0.00..1.20 rows=1 width=1)
Filter: ($2 ~ (pattern)::text)
-> Limit (cost=0.00..1.05 rows=1 width=9)
-> Seq Scan on "_CallsignGroup" (cost=0.00..1.05
rows=1 width=9)
Filter: (group_id = $1)
-> Limit (cost=0.00..2.17 rows=1 width=14)
-> Nested Loop (cost=0.00..2.17 rows=1 width=14)
Join Filter: ("inner".group_id = "outer".group_id)
-> Seq Scan on "_OperatorClass"
(cost=0.00..1.07 rows=1 width=5)
Filter: (class_id = $0)
-> Seq Scan on "_CallsignGroup"
(cost=0.00..1.04 rows=4 width=14)
-> Limit (cost=0.00..1.07 rows=1 width=13)
-> Seq Scan on "_OperatorClass" (cost=0.00..1.07
rows=1 width=13)
Filter: (class_id = $0)

Sorry about the post to pgsql-general; since this appeared to be a 8.0
regression, I posted it there. I guess I should subscribe to
pgsql-perform ... ???

If/when you think this will be fixed in 8.1, I'll be glad to try it
there (assuming there's an RPM build for it). I have three PostgreSQL
servers supporting duplicate copies of the database, and I can easily
take one off-line to test. Obviously no rush, as I've got a perfectly
good work-around.

Note that going back to 7.4.8 is not as easy, as postgresql.conf has
changed going to 8.0, and my installation automatically migrates
configuration files to all servers.

-- Dean

On 2005-10-17 11:56, Tom Lane wrote:
> "Dean Gibson (DB Administrator)" <postgresql4(at)ultimeth(dot)com> writes:
>
>> The following query ran in a fraction of a second on 7.4.8:
>> ...
>> On 8.0.4, it runs for hours (stopped after two hours). Here's the plan:
>>
>
> Do you have the plan used by 7.4?
>
> BTW, this is not really on-topic for pgsql-general; pgsql-perform would
> be a more appropriate forum.
>