Re: Is it possible and worthy to optimize scanRTEForColumn()?

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Andres Freund <andres(at)anarazel(dot)de>
Cc: Rui Hai Jiang <ruihaijiang(at)msn(dot)com>, "pgsql-hackers(at)postgresql(dot)org" <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Is it possible and worthy to optimize scanRTEForColumn()?
Date: 2017-12-08 19:41:14
Message-ID: 2606.1512762074@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Andres Freund <andres(at)anarazel(dot)de> writes:
> On 2017-12-08 10:05:17 -0500, Tom Lane wrote:
>> I'm not particularly concerned about it --- I've not seen profiles
>> suggesting that that function is a big time sink. Tables with very
>> many columns tend to be inefficient for lots of reasons, and I rather
>> doubt that this particular place is the first thing to hit if you
>> want to make that better.

> FWIW, I've definitely seen scanRTEForColumn() show up in profiles. In
> some of those cases the issue could be worked around by mostly using
> prepared statements. But it definitely can be painful, not that
> surprising given the the complexity is essentially
> O(#available-columns * #looked-up-columns).

> However I don't think a microoptimization, even if it were correct, as
> breaking earlier out of the loop would help meaningfully. We'd need a
> different datastructure.

Yeah, if someone were holding a gun on me and saying "make that particular
function faster", I'd think about a hash table rather than scanning a
list. Perhaps a hash table with all the column names exposed by FROM,
not one hash per RTE. However, if you have a FROM that exposes a lot of
column names, and then the query only looks up a few of them, you might
come out behind by building a hash table :-(

I'm still unconvinced that this is the first place to improve for
wide tables, anyway.

regards, tom lane

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Andres Freund 2017-12-08 19:48:54 Re: Is it possible and worthy to optimize scanRTEForColumn()?
Previous Message Andres Freund 2017-12-08 19:35:11 Re: Is it possible and worthy to optimize scanRTEForColumn()?