Re: Planner regression in 8.0.x: WORKAROUND

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