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

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