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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461