ctidscan as an example of custom-scan (Re: [v9.5] Custom Plan API)

From: Kouhei Kaigai <kaigai(at)ak(dot)jp(dot)nec(dot)com>
To: Robert Haas <robertmhaas(at)gmail(dot)com>, Simon Riggs <simon(at)2ndquadrant(dot)com>
Cc: Kohei KaiGai <kaigai(at)kaigai(dot)gr(dot)jp>, PgHacker <pgsql-hackers(at)postgresql(dot)org>
Subject: ctidscan as an example of custom-scan (Re: [v9.5] Custom Plan API)
Date: 2014-12-15 07:55:11
Message-ID: 9A28C8860F777E439AA12E8AEA7694F801091310@BPXM15GP.gisp.nec.co.jp
Views: Raw Message | Whole Thread | Download mbox
Thread:
Lists: pgsql-hackers

This attached patch adds a code example of custom-scan interface.

This custom-scan provider ("ctidscan") performs almost same as
built-in SeqScan plan, but can produce same results with less
page scan in case when qualifier of relation scan has inequality
operators (>, >=, < or <=) on "ctid" system column, therefore,
range to be scanned is less than case of full-table scan.

Below is an example:

postgres=# EXPLAIN SELECT * FROM t1 WHERE ctid > '(10,0)'::tid AND b like '%abc%';
QUERY PLAN
-------------------------------------------------------------
Seq Scan on t1 (cost=0.00..10.00 rows=1 width=37)
Filter: ((ctid > '(10,0)'::tid) AND (b ~~ '%abc%'::text))
(2 rows)

Once ctidscan is loaded, it can provide cheaper cost to scan
the "t1" table than SeqScan, so planner chooses the custom logic.

postgres=# LOAD 'ctidscan';
LOAD
postgres=# EXPLAIN SELECT * FROM t1 WHERE ctid > '(10,0)'::tid AND b like '%abc%';
QUERY PLAN
-----------------------------------------------------------------
Custom Scan (ctidscan) on t1 (cost=0.00..5.00 rows=1 width=37)
Filter: ((ctid > '(10,0)'::tid) AND (b ~~ '%abc%'::text))
ctid quals: (ctid > '(10,0)'::tid)
(3 rows)

Like other query execution logic, it also provides "enable_ctidscan"
parameter to turn on/off this feature.

I'm not certain whether we should have this functionality in contrib
from the perspective of workload that can help, but its major worth is
for an example of custom-scan interface.

Thanks,
--
NEC OSS Promotion Center / PG-Strom Project
KaiGai Kohei <kaigai(at)ak(dot)jp(dot)nec(dot)com>

> -----Original Message-----
> From: Robert Haas [mailto:robertmhaas(at)gmail(dot)com]
> Sent: Thursday, December 11, 2014 11:58 AM
> To: Simon Riggs
> Cc: Kaigai Kouhei(海外 浩平); Thom Brown; Kohei KaiGai; Tom Lane; Alvaro
> Herrera; Shigeru Hanada; Stephen Frost; Andres Freund; PgHacker; Jim
> Mlodgenski; Peter Eisentraut
> Subject: ##freemail## Re: [HACKERS] [v9.5] Custom Plan API
>
> On Tue, Dec 9, 2014 at 3:24 AM, Simon Riggs <simon(at)2ndquadrant(dot)com> wrote:
> > Feedback I am receiving is that the API is unusable. That could be
> > because it is impenetrable, or because it is unusable. I'm not sure it
> > matters which.
>
> It would be nice to here what someone is trying to use it for and what problems
> that person is encountering. Without that, it's pretty much impossible
> for anyone to fix anything.
>
> As for sample code, KaiGai had a working example, which of course got broken
> when Tom changed the API, but it didn't look to me like Tom's changes would
> have made anything impossible that was possible before.
> I'm frankly kind of astonished by the tenor of this entire conversation;
> there is certainly plenty of code in the backend that is less
> self-documenting than this is; and KaiGai did already put up a wiki page
> with documentation as you requested. From his response, it sounds like
> he has updated the ctidscan code, too.
>
> --
> Robert Haas
> EnterpriseDB: http://www.enterprisedb.com The Enterprise PostgreSQL
> Company

Attachment Content-Type Size
pgsql-v9.5-contrib-ctidscan.v1.patch application/octet-stream 45.2 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Michael Paquier 2014-12-15 08:12:10 Re: tracking commit timestamps
Previous Message Amit Langote 2014-12-15 07:04:57 Comment typo in typedef struct BrinTuple