|
| 1 | +From vadim@krs.ru Fri Aug 6 00:02:02 1999 |
| 2 | +Received: from sunpine.krs.ru (SunPine.krs.ru [195.161.16.37]) |
| 3 | +by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id AAA22890 |
| 4 | +for <maillist@candle.pha.pa.us>; Fri, 6 Aug 1999 00:02:00 -0400 (EDT) |
| 5 | +Received: from krs.ru (dune.krs.ru [195.161.16.38]) |
| 6 | +by sunpine.krs.ru (8.8.8/8.8.8) with ESMTP id MAA23302; |
| 7 | +Fri, 6 Aug 1999 12:01:59 +0800 (KRSS) |
| 8 | +Sender: root@sunpine.krs.ru |
| 9 | +Message-ID: <37AA5E35.66C03F2E@krs.ru> |
| 10 | +Date: Fri, 06 Aug 1999 12:01:57 +0800 |
| 11 | +From: Vadim Mikheev <vadim@krs.ru> |
| 12 | +Organization: OJSC Rostelecom (Krasnoyarsk) |
| 13 | +X-Mailer: Mozilla 4.5 [en] (X11; I; FreeBSD 3.0-RELEASE i386) |
| 14 | +X-Accept-Language: ru, en |
| 15 | +MIME-Version: 1.0 |
| 16 | +To: Bruce Momjian <maillist@candle.pha.pa.us> |
| 17 | +CC: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-hackers@postgreSQL.org |
| 18 | +Subject: Re: [HACKERS] Idea for speeding up uncorrelated subqueries |
| 19 | +References: <199908060331.XAA22277@candle.pha.pa.us> |
| 20 | +Content-Type: text/plain; charset=us-ascii |
| 21 | +Content-Transfer-Encoding: 7bit |
| 22 | +Status: RO |
| 23 | + |
| 24 | +Bruce Momjian wrote: |
| 25 | +> |
| 26 | +> Isn't it something that takes only a few hours to implement. We can't |
| 27 | +> keep telling people to us EXISTS, especially because most SQL people |
| 28 | +> think correlated queries are slower that non-correlated ones. Can we |
| 29 | +> just on-the-fly rewrite the query to use exists? |
| 30 | + |
| 31 | +This seems easy to implement. We could look does subquery have |
| 32 | +aggregates or not before calling union_planner() in |
| 33 | +subselect.c:_make_subplan() and rewrite it (change |
| 34 | +slink->subLinkType from IN to EXISTS and add quals). |
| 35 | + |
| 36 | +Without caching implemented IN-->EXISTS rewriting always |
| 37 | +has sence. |
| 38 | + |
| 39 | +After implementation of caching we probably should call union_planner() |
| 40 | +for both original/modified subqueries and compare costs/sizes |
| 41 | +of EXISTS/IN_with_caching plans and maybe even make |
| 42 | +decision what plan to use after parent query is planned |
| 43 | +and we know for how many parent rows subplan will be executed. |
| 44 | + |
| 45 | +Vadim |
| 46 | + |
| 47 | +From tgl@sss.pgh.pa.us Fri Aug 6 00:15:23 1999 |
| 48 | +Received: from sss.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) |
| 49 | +by candle.pha.pa.us (8.9.0/8.9.0) with ESMTP id AAA23058 |
| 50 | +for <maillist@candle.pha.pa.us>; Fri, 6 Aug 1999 00:15:22 -0400 (EDT) |
| 51 | +Received: from sss.sss.pgh.pa.us (localhost [127.0.0.1]) |
| 52 | +by sss.sss.pgh.pa.us (8.9.1/8.9.1) with ESMTP id AAA06786; |
| 53 | +Fri, 6 Aug 1999 00:14:50 -0400 (EDT) |
| 54 | +To: Bruce Momjian <maillist@candle.pha.pa.us> |
| 55 | +cc: Vadim Mikheev <vadim@krs.ru>, pgsql-hackers@postgreSQL.org |
| 56 | +Subject: Re: [HACKERS] Idea for speeding up uncorrelated subqueries |
| 57 | +In-reply-to: Your message of Thu, 5 Aug 1999 23:31:01 -0400 (EDT) |
| 58 | + <199908060331.XAA22277@candle.pha.pa.us> |
| 59 | +Date: Fri, 06 Aug 1999 00:14:50 -0400 |
| 60 | +Message-ID: <6783.933912890@sss.pgh.pa.us> |
| 61 | +From: Tom Lane <tgl@sss.pgh.pa.us> |
| 62 | +Status: RO |
| 63 | + |
| 64 | +Bruce Momjian <maillist@candle.pha.pa.us> writes: |
| 65 | +> Isn't it something that takes only a few hours to implement. We can't |
| 66 | +> keep telling people to us EXISTS, especially because most SQL people |
| 67 | +> think correlated queries are slower that non-correlated ones. Can we |
| 68 | +> just on-the-fly rewrite the query to use exists? |
| 69 | + |
| 70 | +I was just about to suggest exactly that. The "IN (subselect)" |
| 71 | +notation seems to be a lot more intuitive --- at least, people |
| 72 | +keep coming up with it --- so why not rewrite it to the EXISTS |
| 73 | +form, if we can handle that more efficiently? |
| 74 | + |
| 75 | +regards, tom lane |
| 76 | + |