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
