pgsql: Add TID Range Scans to support efficient scanning ranges of TIDs

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

pgsql: Add TID Range Scans to support efficient scanning ranges of TIDs

David Rowley-4
Add TID Range Scans to support efficient scanning ranges of TIDs

This adds a new executor node named TID Range Scan.  The query planner
will generate paths for TID Range scans when quals are discovered on base
relations which search for ranges on the table's ctid column.  These
ranges may be open at either end. For example, WHERE ctid >= '(10,0)';
will return all tuples on page 10 and over.

To support this, two new optional callback functions have been added to
table AM.  scan_set_tidrange is used to set the scan range to just the
given range of TIDs.  scan_getnextslot_tidrange fetches the next tuple
in the given range.

For AMs were scanning ranges of TIDs would not make sense, these functions
can be set to NULL in the TableAmRoutine.  The query planner won't
generate TID Range Scan Paths in that case.

Author: Edmund Horner, David Rowley
Reviewed-by: David Rowley, Tomas Vondra, Tom Lane, Andres Freund, Zhihong Yu
Discussion: https://postgr.es/m/CAMyN-kB-nFTkF=VA_JPwFNo08S0d-Yk0F741S2B7LDmYAi8eyA@...

Branch
------
master

Details
-------
https://git.postgresql.org/pg/commitdiff/bb437f995d47405ecd92cf66df71f7f7e40ed460

Modified Files
--------------
src/backend/access/heap/heapam.c           | 147 ++++++++++
src/backend/access/heap/heapam_handler.c   |   3 +
src/backend/commands/explain.c             |  23 ++
src/backend/executor/Makefile              |   1 +
src/backend/executor/execAmi.c             |   6 +
src/backend/executor/execCurrent.c         |   1 +
src/backend/executor/execProcnode.c        |  10 +
src/backend/executor/nodeTidrangescan.c    | 413 +++++++++++++++++++++++++++++
src/backend/nodes/copyfuncs.c              |  24 ++
src/backend/nodes/outfuncs.c               |  14 +
src/backend/optimizer/README               |   1 +
src/backend/optimizer/path/costsize.c      |  95 +++++++
src/backend/optimizer/path/tidpath.c       | 119 ++++++++-
src/backend/optimizer/plan/createplan.c    |  98 +++++++
src/backend/optimizer/plan/setrefs.c       |  16 ++
src/backend/optimizer/plan/subselect.c     |   6 +
src/backend/optimizer/util/pathnode.c      |  29 ++
src/backend/optimizer/util/plancat.c       |   6 +
src/backend/optimizer/util/relnode.c       |   3 +
src/backend/storage/page/itemptr.c         |  59 +++++
src/include/access/heapam.h                |   6 +-
src/include/access/relscan.h               |   4 +
src/include/access/tableam.h               |  97 ++++++-
src/include/catalog/pg_operator.dat        |   6 +-
src/include/executor/nodeTidrangescan.h    |  24 ++
src/include/nodes/execnodes.h              |  18 ++
src/include/nodes/nodes.h                  |   3 +
src/include/nodes/pathnodes.h              |  18 ++
src/include/nodes/plannodes.h              |  13 +
src/include/optimizer/cost.h               |   3 +
src/include/optimizer/pathnode.h           |   4 +
src/include/storage/itemptr.h              |   2 +
src/test/regress/expected/tidrangescan.out | 300 +++++++++++++++++++++
src/test/regress/parallel_schedule         |   2 +-
src/test/regress/serial_schedule           |   1 +
src/test/regress/sql/tidrangescan.sql      | 101 +++++++
36 files changed, 1654 insertions(+), 22 deletions(-)

Reply | Threaded
Open this post in threaded view
|

Re: pgsql: Add TID Range Scans to support efficient scanning ranges of TIDs

Álvaro Herrera
On 2021-Feb-27, David Rowley wrote:

> Add TID Range Scans to support efficient scanning ranges of TIDs

Hmm, this seems to have missed a catversion bump, and we haven't had one
since it went in ...



--
Álvaro Herrera       Valdivia, Chile
"Find a bug in a program, and fix it, and the program will work today.
Show the program how to find and fix a bug, and the program
will work forever" (Oliver Silfridge)


Reply | Threaded
Open this post in threaded view
|

Re: pgsql: Add TID Range Scans to support efficient scanning ranges of TIDs

Tom Lane-2
Alvaro Herrera <[hidden email]> writes:
> On 2021-Feb-27, David Rowley wrote:
>> Add TID Range Scans to support efficient scanning ranges of TIDs

> Hmm, this seems to have missed a catversion bump, and we haven't had one
> since it went in ...

I've not read the patch in detail, but offhand I don't see why it would
need a catversion bump.  It doesn't look to have changed any persistent
data structures.

                        regards, tom lane


Reply | Threaded
Open this post in threaded view
|

Re: pgsql: Add TID Range Scans to support efficient scanning ranges of TIDs

Álvaro Herrera
On 2021-Mar-01, Tom Lane wrote:

> Alvaro Herrera <[hidden email]> writes:
> > On 2021-Feb-27, David Rowley wrote:
> >> Add TID Range Scans to support efficient scanning ranges of TIDs
>
> > Hmm, this seems to have missed a catversion bump, and we haven't had one
> > since it went in ...
>
> I've not read the patch in detail, but offhand I don't see why it would
> need a catversion bump.  It doesn't look to have changed any persistent
> data structures.

Oh, so it appears you're right -- there are NodeTag renumberings but
those aren't stored anywhere.

--
Álvaro Herrera       Valdivia, Chile
Bob [Floyd] used to say that he was planning to get a Ph.D. by the "green
stamp method," namely by saving envelopes addressed to him as 'Dr. Floyd'.
After collecting 500 such letters, he mused, a university somewhere in
Arizona would probably grant him a degree.              (Don Knuth)