Prev: - GSoC - snapshot materialized view (work-in-progress) patch
Next: [HACKERS] dblink regression failure in HEAD
From: Hitoshi Harada on 16 Jul 2010 12:15
2010/7/17 Marko Tiikkaja <marko.tiikkaja(a)cs.helsinki.fi>:
> On 7/16/10 6:15 PM +0300, Hitoshi Harada wrote:
>>
>> 1. Use MaterialNode instead of adding DtScanNode. Since MaterialNode
>> is exsiting one that work with single tuplestore, it might be sane to
>> modify this so that it accepts tuplestore from Query instead of its
>> child node.
>
> I thought about this, but I don't necessarily like the idea of overloading
> executor nodes.

Neither do I have good shape for this solution. Maybe it's not good
idea. But my concern is adding DtScanNode, which looks similar to
MaterialNode. Of course each purpose is different, but quite big part
will overlap each other, I think.

>> 2. Use temp table instead of tuplestore list. Since we agreed we need
>> to execute each plan one by one starting and shutting down executor,
>> it now looks very simple strategy.
>
> I didn't look at this because I thought using a "tuplestore receiver" in the
> portal logic was simple enough. �Any thoughts on how this would work?

It's just deconstructing queries like:

WITH t AS (INSERT INTO x ... RETURING *)
SELECT * FROM t;

to

CREATE TEMP TABLE t AS INSERT INTO x ... RETURING *;
SELECT * FROM t;

While the second statement is not implemented yet, it will be so simpler.

Another concern is tuplestore's memory exhausting. Tuplestore holds
tuples in memory as far as the estimated memory usage is within
work_mem (for *each* not total of all tuplestores!), but if you create
dozens of tuplestore (and it's quite possible in wCTE use cases) we
will possibly fail into memory overflow problems.

>> I'm not familiar with the long discussion on this feature so not sure
>> they are possible, but ISTM �they are enough to be discussed (or
>> discussed already?).
>
> We haven't discussed this part of the design yet.. �Now is a good time to do
> it.

Yeah, we should. Anyone has another idea? Or adding DtScanNode for
this features is fair enough?


Regards,


--
Hitoshi Harada

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Marko Tiikkaja on 16 Jul 2010 12:31
On 7/16/10 7:15 PM +0300, Hitoshi Harada wrote:
> 2010/7/17 Marko Tiikkaja<marko.tiikkaja(a)cs.helsinki.fi>:
>> I thought about this, but I don't necessarily like the idea of overloading
>> executor nodes.
>
> Neither do I have good shape for this solution. Maybe it's not good
> idea. But my concern is adding DtScanNode, which looks similar to
> MaterialNode. Of course each purpose is different, but quite big part
> will overlap each other, I think.

The way I see it is that reading from a tuplestore is so simple that we
shouldn't be trying to merge together nodes just on that basis. It
seems to me that we'd have to add CteScan and WorkTableScan nodes there
too and at that point it would become complicated.

>> I didn't look at this because I thought using a "tuplestore receiver" in the
>> portal logic was simple enough. Any thoughts on how this would work?
>
> It's just deconstructing queries like:
>
> WITH t AS (INSERT INTO x ... RETURING *)
> SELECT * FROM t;
>
> to
>
> CREATE TEMP TABLE t AS INSERT INTO x ... RETURING *;
> SELECT * FROM t;

That's an idea. Can we somehow avoid name clashes with user-defined
temporary tables?

> Another concern is tuplestore's memory exhausting. Tuplestore holds
> tuples in memory as far as the estimated memory usage is within
> work_mem (for *each* not total of all tuplestores!), but if you create
> dozens of tuplestore (and it's quite possible in wCTE use cases) we
> will possibly fail into memory overflow problems.

That doesn't seem very different from a big SELECT query, except with
wCTEs, you actually *know* how many times the work_mem can be used
before you run the query and can adjust work_mem accordingly.

That said, I personally could live with a separate GUC for just
adjusting the work_mem of "wcte tuplestores". Another option would be
to unconditionally force the tuplestores to disk, but that sounds painful.


Regards,
Marko Tiikkaja

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: David Fetter on 20 Jul 2010 16:49
On Sat, Jul 17, 2010 at 01:15:22AM +0900, Hitoshi Harada wrote:
> 2010/7/17 Marko Tiikkaja <marko.tiikkaja(a)cs.helsinki.fi>:
> > On 7/16/10 6:15 PM +0300, Hitoshi Harada wrote:
> >>
> >> 1. Use MaterialNode instead of adding DtScanNode. Since MaterialNode
> >> is exsiting one that work with single tuplestore, it might be sane to
> >> modify this so that it accepts tuplestore from Query instead of its
> >> child node.
> >
> > I thought about this, but I don't necessarily like the idea of overloading
> > executor nodes.
>
> Neither do I have good shape for this solution. Maybe it's not good
> idea. But my concern is adding DtScanNode, which looks similar to
> MaterialNode. Of course each purpose is different, but quite big part
> will overlap each other, I think.

Any ideas as to how to factor this out? Handiest ideas would be in
the form of a patch ;)

> >> 2. Use temp table instead of tuplestore list. Since we agreed we need
> >> to execute each plan one by one starting and shutting down executor,
> >> it now looks very simple strategy.
> >
> > I didn't look at this because I thought using a "tuplestore receiver" in the
> > portal logic was simple enough. �Any thoughts on how this would work?
>
> It's just deconstructing queries like:
>
> WITH t AS (INSERT INTO x ... RETURING *)
> SELECT * FROM t;
>
> to
>
> CREATE TEMP TABLE t AS INSERT INTO x ... RETURING *;
> SELECT * FROM t;
>
> While the second statement is not implemented yet, it will be so simpler.

So CTAS would get expanded into CTA[row-returning query] ?
Interesting. How much work would that part be?

Cheers,
David.
--
David Fetter <david(a)fetter.org> http://fetter.org/
Phone: +1 415 235 3778 AIM: dfetter666 Yahoo!: dfetter
Skype: davidfetter XMPP: david.fetter(a)gmail.com
iCal: webcal://www.tripit.com/feed/ical/people/david74/tripit.ics

Remember to vote!
Consider donating to Postgres: http://www.postgresql.org/about/donate

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Merlin Moncure on 20 Jul 2010 17:13
On Fri, Jul 16, 2010 at 12:15 PM, Hitoshi Harada <umi.tanuki(a)gmail.com> wrote:
> 2010/7/17 Marko Tiikkaja <marko.tiikkaja(a)cs.helsinki.fi>:
>> On 7/16/10 6:15 PM +0300, Hitoshi Harada wrote:
>>>
>>> 1. Use MaterialNode instead of adding DtScanNode. Since MaterialNode
>>> is exsiting one that work with single tuplestore, it might be sane to
>>> modify this so that it accepts tuplestore from Query instead of its
>>> child node.
>>
>> I thought about this, but I don't necessarily like the idea of overloading
>> executor nodes.
>
> Neither do I have good shape for this solution. Maybe it's not good
> idea. But my concern is adding DtScanNode, which looks similar to
> MaterialNode. Of course each purpose is different, but quite big part
> will overlap each other, I think.
>
>>> 2. Use temp table instead of tuplestore list. Since we agreed we need
>>> to execute each plan one by one starting and shutting down executor,
>>> it now looks very simple strategy.
>>
>> I didn't look at this because I thought using a "tuplestore receiver" in the
>> portal logic was simple enough. �Any thoughts on how this would work?
>
> It's just deconstructing queries like:
>
> WITH t AS (INSERT INTO x ... RETURING *)
> SELECT * FROM t;
>
> to
>
> CREATE TEMP TABLE t AS INSERT INTO x ... RETURING *;
> SELECT * FROM t;

Is it acceptable for a wCTE query to manipulate the system catalogs?
Couldn't this cause performance issues in some cases?

merlin

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

From: Robert Haas on 20 Jul 2010 20:34
On Tue, Jul 20, 2010 at 5:13 PM, Merlin Moncure <mmoncure(a)gmail.com> wrote:
>>>> 2. Use temp table instead of tuplestore list. Since we agreed we need
>>>> to execute each plan one by one starting and shutting down executor,
>>>> it now looks very simple strategy.
>>>
>>> I didn't look at this because I thought using a "tuplestore receiver" in the
>>> portal logic was simple enough. �Any thoughts on how this would work?
>>
>> It's just deconstructing queries like:
>>
>> WITH t AS (INSERT INTO x ... RETURING *)
>> SELECT * FROM t;
>>
>> to
>>
>> CREATE TEMP TABLE t AS INSERT INTO x ... RETURING *;
>> SELECT * FROM t;
>
> Is it acceptable for a wCTE query to manipulate the system catalogs?
> Couldn't this cause performance issues in some cases?

Yeah, I suspect the performance wouldn't be too hot. I think the idea
of writing into a tuplestore and then reading back out from it is the
way to go.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company

--
Sent via pgsql-hackers mailing list (pgsql-hackers(a)postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: - GSoC - snapshot materialized view (work-in-progress) patch
Next: [HACKERS] dblink regression failure in HEAD