Re: [HACKERS] Secondary index access optimizations

From: Konstantin Knizhnik <k(dot)knizhnik(at)postgrespro(dot)ru>
To: Thomas Munro <thomas(dot)munro(at)enterprisedb(dot)com>
Cc: Antonin Houska <ah(at)cybertec(dot)at>, pgsql-hackers(at)lists(dot)postgresql(dot)org
Subject: Re: [HACKERS] Secondary index access optimizations
Date: 2018-01-29 16:46:13
Message-ID: e77e94e2-406c-4b59-f4be-1401d6843599@postgrespro.ru
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On 29.01.2018 16:24, Konstantin Knizhnik wrote:
> On 29.01.2018 07:34, Thomas Munro wrote:
>> On Sat, Jan 20, 2018 at 5:41 AM, Konstantin Knizhnik
>> <k(dot)knizhnik(at)postgrespro(dot)ru> wrote:
>>> On 19.01.2018 16:14, Antonin Houska wrote:
>>>> you should test the operator B-tree strategy: BTLessStrategyNumber,
>>>> BTLessEqualStrategyNumber, etc. The operator string alone does not
>>>> tell
>>>> enough
>>>> about the operator semantics.
>>>>
>>>> The strategy can be found in the pg_amop catalog.
>>>> get_op_btree_interpretation() function may be useful, but there may be
>>>> something better in utils/cache/lsyscache.c.
>>>>
>>> Thank you very much.
>>> Shame on me that I didn't notice such solution myself - such
>>> checking of
>>> B-tree strategy is done in the same predtest.c file!
>>> Now checking of predicate clauses compatibility is done in much more
>>> elegant
>>> and general way.
>>> Attached please find new version of the patch.
>> I wonder if you should create a new index and SysCache entry for
>> looking up range types by subtype.  I'll be interested to see what
>> others have to say about this range type-based technique -- it seems
>> clever to me, but I'm not familiar enough with this stuff to say if
>> there is some other approach you should be using instead.
> I think that it is good idea to add caching for range type lookup.
> If community think that it will be useful I can try to add such
> mechanism.
> But it seems to be not so trivial, especially properly handle
> invalidations.
>

I have added caching of element_type->range_type mapping to typcache.c.
But it is not invalidated now if pg_range relation is changed.
Also if there are more than one range types for the specified element
type then first of them is used.

--

Konstantin Knizhnik
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company

Attachment Content-Type Size
optimizer-10.patch text/x-patch 49.6 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2018-01-29 16:50:02 Re: [HACKERS] MERGE SQL Statement for PG11
Previous Message Bruce Momjian 2018-01-29 16:44:22 Re: [HACKERS] MERGE SQL Statement for PG11