Re: Planner regression in 8.0.x: WORKAROUND

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