Re: Proposal: scan key push down to heap [WIP]

From: Dilip Kumar <dilipbalaut(at)gmail(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>
Cc: Kouhei Kaigai <kaigai(at)ak(dot)jp(dot)nec(dot)com>, Andres Freund <andres(at)anarazel(dot)de>, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, Alvaro Herrera <alvherre(at)2ndquadrant(dot)com>, pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: Proposal: scan key push down to heap [WIP]
Date: 2016-11-30 10:41:23
Message-ID: CAFiTN-takT6Z4s3tGDwyC9bhYf+1gumpvW5bo_fpeNUy+rL-kg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

On Tue, Nov 29, 2016 at 11:21 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> On Mon, Nov 28, 2016 at 11:17 PM, Dilip Kumar <dilipbalaut(at)gmail(dot)com> wrote:
>> Actually we want to call slot_getattr instead heap_getattr, because of
>> problem mentioned by Andres upthread and we also saw in test results.
>
> Ah, right.
>
>> Should we make a copy of HeapKeyTest lets say ExecKeyTest and keep it
>> under executor ?
>
> Sure.

I have worked on the idea you suggested upthread. POC patch is attached.

Todo:
1. Comments.
2. Test.
3. Some regress output will change as we are adding some extra
information to analyze output.

I need some suggestion..

1. As we decided to separate scankey and qual during planning time. So
I am doing it at create_seqscan_path. My question is currently we
don't have path node for seqscan, so where should we store scankey ?
In Path node, or create new SeqScanPath node ?. In attached patch I
have stored in Path node.

2. This is regarding instrumentation information for scan key, after
my changes currently explain analyze result will look like this.

postgres=# explain (analyze, costs off) select * from lineitem
where l_shipmode in ('MAIL', 'AIR')
and l_receiptdate >= date '1994-01-01';
QUERY PLAN
--------------------------------------------------------------------------
Seq Scan on lineitem (actual time=0.022..12179.946 rows=6238212 loops=1)
Scan Key: (l_receiptdate >= '1994-01-01'::date)
Filter: (l_shipmode = ANY ('{MAIL,AIR}'::bpchar[]))
Rows Removed by Scan Key: 8162088
Rows Removed by Filter: 15599495
Planning time: 0.182 ms
Execution time: 12410.529 ms

My question is, how should we show pushdown filters ?
In above plan I am showing as "Scan Key: ", does this look fine or we
should name it something else ?

--
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com

Attachment Content-Type Size
heap_scankey_pushdown_POC_v4.patch application/octet-stream 15.2 KB

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Amit Langote 2016-11-30 10:53:32 Re: Declarative partitioning - another take
Previous Message Etsuro Fujita 2016-11-30 09:05:44 Re: postgres_fdw : altering foreign table not invalidating prepare statement execution plan.