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@xxxxxxxxxxxxx Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---------------------------(end of broadcast)--------------------------- TIP 6: explain analyze is your friend
