All geek questions in one place

Can index-organized tables pave the way for a faster SELECT COUNT (*) FROM table

It's hard for me to create a trigger to get the current full rows of the table without making the table COUNT (*) FROM. I think if their planned indexed tables for Postgres 8.5 could make this possible?

+3
source share
2 answers

I would not have thought that an index-organized table would certainly scan faster to count all visible tuples. Logically, it would have to go through the same amount of data, regardless of whether it is organized so that the data is in leaf nodes of the b-tree or in the existing heap format.

Currently, postgresql indexes only store [key, ctid] pairs (essentially). (Cid is essentially "rowid" - the page number of the heap and the index of the tuple's line pointer). Thus, you cannot count the rows in the table just by looking at the index, because you need to check [xmin, xmax] for each tuple - and which is stored only with data on the heap.

[xmin, xmax] , - . , , , / , : , , , , , . , , tsvector , , , , . , , . , - - 8.3 ( ).

, , [xmin, xmax] - . pkey . , , . .

- , , , Oracle ( SQL Server, ) , [key, tuple] ( [, pkey] ) - ctid, . , "" [xmin, xmax, cminmax, natts,....] .., "select count (*) ", . , - , "".

AFAICT , , , 1 3, . , , , Oracle, IOT, , , , - , ( , Oracle [ , pkey] IOT, - rowid).

+3

, PostgreSQL 9.2 , count (*).

.

0

Source: https://habr.com/ru/post/1707930/

More articles:


All Articles