Search Postgresql Archives

Re: Planner regression in 8.0.x ?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux