Re: Planner regression in 8.0.x ? - Mailing list pgsql-general

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@ultimeth.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.
>