Thread: Planner regression in 8.0.x ?
Last night I upgraded my three DB servers from 7.4.8 to 8.0.4 (RPM from
the PostgreSQL site). This morning I found my servers very busy from
three queries that were two hours old:
The following query ran in a fraction of a second on 7.4.8:
SELECT receipt_date, process_date, callsign AS applicant_callsign,
operator_class, geo_region, uls_file_num,
vanity_callsign, prediction, predict_level AS _level, licensee_id AS
_lid,
operator_group AS _oper_group, vanity_group AS _vanity_group,
vanity_region AS _vanity_region, usps AS _usps, end_date AS _end_date,
(SELECT TRUE FROM archivejb WHERE ( (callsign = gen.callsign AND
license_status = 'A' AND prev_callsign = gen.vanity_callsign)
OR (callsign =
gen.vanity_callsign AND licensee_id =
gen.licensee_id))
AND grant_date < receipt_date LIMIT
1) AS _verified,
(SELECT TRUE FROM "ReservedCall" WHERE vanity_callsign ~ pattern
LIMIT 1) AS _reserved, radio_service AS _service
FROM genapp_pending_ AS gen WHERE vanity_type::CHAR = 'A'
ORDER BY receipt_date DESC, SUBSTRING( callsign, '[0-9]' ), callsign,
uls_file_num DESC, seq_num
On 8.0.4, it runs for hours (stopped after two hours). Here's the plan:
Sort (cost=921303.61..921303.61 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..921303.60 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)
-> Limit (cost=455905.32..459495.68 rows=1 width=0)
-> Subquery Scan archivejb (cost=455905.32..498989.60
rows=12 width=0)
Filter: (((callsign = $4) AND (license_status =
'A'::bpchar) AND (prev_callsign = $2)) OR ((callsign = $2) AND
(licensee_id = $5)))
-> Unique (cost=455905.32..488509.64
rows=465776 width=229)
-> Sort (cost=455905.32..457069.76
rows=465776 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..194809.19
rows=465776 width=229)
-> Subquery Scan "*SELECT* 1"
(cost=0.00..75019.36 rows=1 width=229)
-> Nested Loop
(cost=0.00..75019.35 rows=1 width=229)
Join Filter:
("outer".callsign = "inner".callsign)
-> Merge Join
(cost=0.00..75013.39 rows=1 width=216)
Merge Cond:
("outer".unique_system_identifier = "inner".unique_system_identifier)
Join Filter:
("outer".callsign = "inner".callsign)
-> Index
Scan using "_EN_pkey" on "_EN" (cost=0.00..37158.11 rows=911646 width=158)
-> Index
Scan using "_AM_pkey" on "_AM" (cost=0.00..21972.80 rows=906891 width=58)
-> Index Scan
using "_HD_pkey" on "_HD" (cost=0.00..5.91 rows=1 width=49)
Index Cond:
("outer".unique_system_identifier = "_HD".unique_system_identifier)
Filter:
(grant_date < $6)
-> Subquery Scan "*SELECT* 2"
(cost=0.00..119789.84 rows=465775 width=186)
-> Seq Scan on "_Lic"
(cost=0.00..115132.09 rows=465775 width=186)
Filter:
((COALESCE((grant_date)::timestamp without time zone, (expire_date - '10
years'::interval)))::date < $6)
-> 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)
In the query, if I remove the "(SELECT TRUE FROM archivejb ... LIMIT 1)
AS _verified", the query runs in a fraction of a second.
"archivejb" is a UNION of one VIEW (JOIN of three tables, w/ 0.9 million
rows) and one TABLE (1.3 million rows). All the other tables are tiny
(<100 rows).
If I can't fix this, I'll have to go back to 7.4.8.
HELP!
-- Dean
In the query below, if I replace:
(SELECT TRUE FROM archivejb WHERE ( (callsign = gen.callsign AND
license_status = 'A' AND prev_callsign = gen.vanity_callsign)
OR (callsign =
gen.vanity_callsign AND licensee_id =
gen.licensee_id))
AND grant_date < receipt_date LIMIT
1) AS _verified,
with:
(SELECT TRUE FROM archivejb WHERE callsign = gen.callsign AND
license_status = 'A' AND prev_callsign = gen.vanity_callsign
AND grant_date < receipt_date LIMIT
1) OR
(SELECT TRUE FROM archivejb WHERE callsign =
gen.vanity_callsign AND licensee_id = gen.licensee_id
AND grant_date < receipt_date LIMIT
1) AS _verified,
then the complete query runs in a fraction of a second, as before. Weird.
I'll be trying additional logical equivalents to try to simplify the
second form while retaining its performance, but why is this happening?
I also have a nightly update (with some equally complex logical
expressions) that used to run in nine minutes, that now runs in eleven
minutes. Not a big deal, but something's changed for the worse here.
On the plus side, it appears that the weekly reload of the three tables
w/ 0.9 million rows (mentioned below) plus construction of several
indexes, now runs in about ten minutes under 8.0.4, as opposed to about
fifteen minutes under 7.4.8.
-- Dean
On 2005-10-17 09:35, Dean Gibson (DB Administrator) wrote:
> Last night I upgraded my three DB servers from 7.4.8 to 8.0.4 (RPM
> from the PostgreSQL site). This morning I found my servers very busy
> from three queries that were two hours old:
>
> The following query ran in a fraction of a second on 7.4.8:
>
> SELECT receipt_date, process_date, callsign AS applicant_callsign,
> operator_class, geo_region, uls_file_num,
> vanity_callsign, prediction, predict_level AS _level, licensee_id AS
> _lid,
> operator_group AS _oper_group, vanity_group AS _vanity_group,
> vanity_region AS _vanity_region, usps AS _usps, end_date AS _end_date,
> (SELECT TRUE FROM archivejb WHERE ( (callsign = gen.callsign
> AND license_status = 'A' AND prev_callsign = gen.vanity_callsign)
> OR (callsign =
> gen.vanity_callsign AND licensee_id =
> gen.licensee_id))
> AND grant_date < receipt_date LIMIT
> 1) AS _verified,
> (SELECT TRUE FROM "ReservedCall" WHERE vanity_callsign ~ pattern
> LIMIT 1) AS _reserved, radio_service AS _service
> FROM genapp_pending_ AS gen WHERE vanity_type::CHAR = 'A'
> ORDER BY receipt_date DESC, SUBSTRING( callsign, '[0-9]' ), callsign,
> uls_file_num DESC, seq_num
>
> On 8.0.4, it runs for hours (stopped after two hours). Here's the plan:
>
> [snip]
>
> In the query, if I remove the "(SELECT TRUE FROM archivejb ... LIMIT
> 1) AS _verified", the query runs in a fraction of a second.
>
> "archivejb" is a UNION of one VIEW (JOIN of three tables, w/ 0.9
> million rows) and one TABLE (1.3 million rows). All the other tables
> are tiny (<100 rows).
>
> If I can't fix this, I'll have to go back to 7.4.8.
>
> HELP!
>
> -- Dean
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
Those two queries aren't the same. The first one can only return 0 or 1 rows; the second one can return 0, 1, or 2 rows. An explain analyze of each should show why one is much faster than the other. On Mon, Oct 17, 2005 at 10:29:43AM -0700, Dean Gibson (DB Administrator) wrote: > In the query below, if I replace: > > (SELECT TRUE FROM archivejb WHERE ( (callsign = gen.callsign AND > license_status = 'A' AND prev_callsign = gen.vanity_callsign) > OR (callsign = > gen.vanity_callsign AND licensee_id = > gen.licensee_id)) > AND grant_date < receipt_date LIMIT > 1) AS _verified, > > with: > > (SELECT TRUE FROM archivejb WHERE callsign = gen.callsign AND > license_status = 'A' AND prev_callsign = gen.vanity_callsign > AND grant_date < receipt_date LIMIT > 1) OR > (SELECT TRUE FROM archivejb WHERE callsign = > gen.vanity_callsign AND licensee_id = gen.licensee_id > AND grant_date < receipt_date LIMIT > 1) AS _verified, > > then the complete query runs in a fraction of a second, as before. Weird. > > I'll be trying additional logical equivalents to try to simplify the > second form while retaining its performance, but why is this happening? > I also have a nightly update (with some equally complex logical > expressions) that used to run in nine minutes, that now runs in eleven > minutes. Not a big deal, but something's changed for the worse here. > > On the plus side, it appears that the weekly reload of the three tables > w/ 0.9 million rows (mentioned below) plus construction of several > indexes, now runs in about ten minutes under 8.0.4, as opposed to about > fifteen minutes under 7.4.8. > > -- Dean > > On 2005-10-17 09:35, Dean Gibson (DB Administrator) wrote: > >Last night I upgraded my three DB servers from 7.4.8 to 8.0.4 (RPM > >from the PostgreSQL site). This morning I found my servers very busy > >from three queries that were two hours old: > > > >The following query ran in a fraction of a second on 7.4.8: > > > >SELECT receipt_date, process_date, callsign AS applicant_callsign, > >operator_class, geo_region, uls_file_num, > > vanity_callsign, prediction, predict_level AS _level, licensee_id AS > >_lid, > > operator_group AS _oper_group, vanity_group AS _vanity_group, > >vanity_region AS _vanity_region, usps AS _usps, end_date AS _end_date, > > (SELECT TRUE FROM archivejb WHERE ( (callsign = gen.callsign > >AND license_status = 'A' AND prev_callsign = gen.vanity_callsign) > > OR (callsign = > >gen.vanity_callsign AND licensee_id = > >gen.licensee_id)) > > AND grant_date < receipt_date LIMIT > >1) AS _verified, > > (SELECT TRUE FROM "ReservedCall" WHERE vanity_callsign ~ pattern > >LIMIT 1) AS _reserved, radio_service AS _service > >FROM genapp_pending_ AS gen WHERE vanity_type::CHAR = 'A' > >ORDER BY receipt_date DESC, SUBSTRING( callsign, '[0-9]' ), callsign, > >uls_file_num DESC, seq_num > > > >On 8.0.4, it runs for hours (stopped after two hours). Here's the plan: > > > >[snip] > > > >In the query, if I remove the "(SELECT TRUE FROM archivejb ... LIMIT > >1) AS _verified", the query runs in a fraction of a second. > > > >"archivejb" is a UNION of one VIEW (JOIN of three tables, w/ 0.9 > >million rows) and one TABLE (1.3 million rows). All the other tables > >are tiny (<100 rows). > > > >If I can't fix this, I'll have to go back to 7.4.8. > > > >HELP! > > > >-- Dean > > > >---------------------------(end of broadcast)--------------------------- > >TIP 3: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
"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.
regards, tom lane
NOT TRUE! The second query is effectively "(SELECT TRUE ...WHERE ... LIMIT 1) OR (SELECT TRUE ...WHERE ... LIMIT 1) AS ..." The first portion in parentheses can return either a single row of TRUE, or no row (NULL). Ditto for the second portion. The OR means that you logically combine TRUEs and/or NULLs into a SINGLE value. This can be trivially verified by the following SELECT: select (select true from anytable where TRUE limit 1) or (select true from anytable where TRUE limit 1); Vary the capitalized "TRUE"s each between true and false, and you will see that EXACTLY ONE ROW IS RETURNED IN EACH CASE, having a resultant (combined) value of either TRUE or NULL. The real issue here is why the original query executes in a fraction of a second under 7.4.x, and runs for hours on 8.0.4. -- Dean On 2005-10-17 11:17, Jim C. Nasby wrote: > Those two queries aren't the same. The first one can only return 0 or 1 rows; > the second one can return 0, 1, or 2 rows. > > An explain analyze of each should show why one is much faster than the > other. > > On Mon, Oct 17, 2005 at 10:29:43AM -0700, Dean Gibson (DB Administrator) wrote: > >> In the query below, if I replace: >> >> (SELECT TRUE FROM archivejb WHERE ( (callsign = gen.callsign AND >> license_status = 'A' AND prev_callsign = gen.vanity_callsign) >> OR (callsign = >> gen.vanity_callsign AND licensee_id = >> gen.licensee_id)) >> AND grant_date < receipt_date LIMIT >> 1) AS _verified, >> >> with: >> >> (SELECT TRUE FROM archivejb WHERE callsign = gen.callsign AND >> license_status = 'A' AND prev_callsign = gen.vanity_callsign >> AND grant_date < receipt_date LIMIT >> 1) OR >> (SELECT TRUE FROM archivejb WHERE callsign = >> gen.vanity_callsign AND licensee_id = gen.licensee_id >> AND grant_date < receipt_date LIMIT >> 1) AS _verified, >>
I stand corrected; I read through the example too quickly. As I said, explain analyze would show what the difference is between the two queries. I suspect that the planner doesn't know these are equivalent, because in the general case of selecting more than just TRUE they wouldn't be. No idea why this is different from 7.4.8, but a lot of code has changed in that timespan. In any case, you're more likely to get help from a developer if you post the full query, and EXPLAIN ANALYZE for each case. Please include any view definitions as well. On Mon, Oct 17, 2005 at 11:45:04AM -0700, Dean Gibson (DB Administrator) wrote: > NOT TRUE! > > The second query is effectively "(SELECT TRUE ...WHERE ... LIMIT 1) OR > (SELECT TRUE ...WHERE ... LIMIT 1) AS ..." > > The first portion in parentheses can return either a single row of TRUE, > or no row (NULL). Ditto for the second portion. The OR means that you > logically combine TRUEs and/or NULLs into a SINGLE value. This can be > trivially verified by the following SELECT: > > select (select true from anytable where TRUE limit 1) or (select true > from anytable where TRUE limit 1); > > Vary the capitalized "TRUE"s each between true and false, and you will > see that EXACTLY ONE ROW IS RETURNED IN EACH CASE, having a resultant > (combined) value of either TRUE or NULL. > > The real issue here is why the original query executes in a fraction of > a second under 7.4.x, and runs for hours on 8.0.4. > > -- Dean > > > On 2005-10-17 11:17, Jim C. Nasby wrote: > >Those two queries aren't the same. The first one can only return 0 or 1 > >rows; > >the second one can return 0, 1, or 2 rows. > > > >An explain analyze of each should show why one is much faster than the > >other. > > > >On Mon, Oct 17, 2005 at 10:29:43AM -0700, Dean Gibson (DB Administrator) > >wrote: > > > >>In the query below, if I replace: > >> > >> (SELECT TRUE FROM archivejb WHERE ( (callsign = gen.callsign AND > >>license_status = 'A' AND prev_callsign = gen.vanity_callsign) > >> OR (callsign = > >>gen.vanity_callsign AND licensee_id = > >>gen.licensee_id)) > >> AND grant_date < receipt_date LIMIT > >>1) AS _verified, > >> > >>with: > >> > >> (SELECT TRUE FROM archivejb WHERE callsign = gen.callsign AND > >>license_status = 'A' AND prev_callsign = gen.vanity_callsign > >> AND grant_date < receipt_date LIMIT > >>1) OR > >> (SELECT TRUE FROM archivejb WHERE callsign = > >>gen.vanity_callsign AND licensee_id = > >>gen.licensee_id > >> AND grant_date < receipt_date LIMIT > >>1) AS _verified, > >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: don't forget to increase your free space map settings > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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.
>
"Dean Gibson (DB Administrator)" <postgresql4@ultimeth.com> writes:
> 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:
Dean, you're wasting our time with the workaround. What we want to know
about is the problem query, and without some evidence about the reason
for the discrepancy between 7.4 and 8.0, we can't make any progress on
that. If you don't have the ability to generate an explain report on
7.4 anymore, at least give us the full schema dump so someone else can
try to reproduce the problem without unreasonable amounts of guesswork.
regards, tom lane