Sfrgttk

Difference between "generating set" and free product?

Is it ok to offer lower paid work as a trial period before negotiating for a full-time job?

How are presidential pardons supposed to be used?

Do warforged have souls?

Are my PIs rude or am I just being too sensitive?

Do working physicists consider Newtonian mechanics to be "falsified"?

Didn't get enough time to take a Coding Test - what to do now?

Am I ethically obligated to go into work on an off day if the reason is sudden?

What is special about square numbers here?

What's the point in a preamp?

How to copy the contents of all files with a certain name into a new file?

Why does this iterative way of solving of equation work?

Cooking pasta in a water boiler

How to test the equality of two Pearson correlation coefficients computed from the same sample?

Wolves and sheep

Does Parliament hold absolute power in the UK?

Did God make two great lights or did He make the great light two?

What was the last x86 CPU that did not have the x87 floating-point unit built in?

In horse breeding, what is the female equivalent of putting a horse out "to stud"?

Can a 1st-level character have an ability score above 18?

What information about me do stores get via my credit card?

Why is the object placed in the middle of the sentence here?

how can a perfect fourth interval be considered either consonant or dissonant?

What aspect of planet Earth must be changed to prevent the industrial revolution?

PostgreSQL Index Only Scan can NOT select ctid

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}

0

I have table A (id bigint, text text), and I have primary key btree on id.

Now I want to fetch ctid of records meet some condition like id = 823977776533426178.

However, even using pg_hint_plan to hint PostgreSQL to use Index Only Scan, it will always give me Index Scan, like the following:

/*+ IndexOnlyScan(a) */ explain analyze select ctid from a where id = 823977776533426178;

                                                QUERY PLAN                                                

----------------------------------------------------------------------------------------------------------

 Index Scan using a_pkey on a  (cost=0.14..8.16 rows=1 width=6) (actual time=0.038..0.039 rows=1 loops=1)

   Index Cond: (id = '823977776533426178'::bigint)

 Planning Time: 0.122 ms

 Execution Time: 0.095 ms

(4 rows)

My understanding is ctid is the physical record id (block_no: record_offset) of each record and it must be included in any btree index, since any index will need this information to fetch the record from heap file.

Then why can't an index only scan return the ctid directly?
Is it just that PostgreSQL does not implement it that way?

BTW, my testing version is PostgreSQL-11.

Thank you!

postgresql postgresql-performance postgresql-11

share|improve this question

asked 11 mins ago

Qiushi BaiQiushi Bai

83

add a comment | 

0

I have table A (id bigint, text text), and I have primary key btree on id.

Now I want to fetch ctid of records meet some condition like id = 823977776533426178.

However, even using pg_hint_plan to hint PostgreSQL to use Index Only Scan, it will always give me Index Scan, like the following:

/*+ IndexOnlyScan(a) */ explain analyze select ctid from a where id = 823977776533426178;

                                                QUERY PLAN                                                

----------------------------------------------------------------------------------------------------------

 Index Scan using a_pkey on a  (cost=0.14..8.16 rows=1 width=6) (actual time=0.038..0.039 rows=1 loops=1)

   Index Cond: (id = '823977776533426178'::bigint)

 Planning Time: 0.122 ms

 Execution Time: 0.095 ms

(4 rows)

My understanding is ctid is the physical record id (block_no: record_offset) of each record and it must be included in any btree index, since any index will need this information to fetch the record from heap file.

Then why can't an index only scan return the ctid directly?
Is it just that PostgreSQL does not implement it that way?

BTW, my testing version is PostgreSQL-11.

Thank you!

postgresql postgresql-performance postgresql-11

share|improve this question

asked 11 mins ago

Qiushi BaiQiushi Bai

83

add a comment | 

I have table A (id bigint, text text), and I have primary key btree on id.

Now I want to fetch ctid of records meet some condition like id = 823977776533426178.

However, even using pg_hint_plan to hint PostgreSQL to use Index Only Scan, it will always give me Index Scan, like the following:

/*+ IndexOnlyScan(a) */ explain analyze select ctid from a where id = 823977776533426178;

                                                QUERY PLAN                                                

----------------------------------------------------------------------------------------------------------

 Index Scan using a_pkey on a  (cost=0.14..8.16 rows=1 width=6) (actual time=0.038..0.039 rows=1 loops=1)

   Index Cond: (id = '823977776533426178'::bigint)

 Planning Time: 0.122 ms

 Execution Time: 0.095 ms

(4 rows)

My understanding is ctid is the physical record id (block_no: record_offset) of each record and it must be included in any btree index, since any index will need this information to fetch the record from heap file.

Then why can't an index only scan return the ctid directly?
Is it just that PostgreSQL does not implement it that way?

BTW, my testing version is PostgreSQL-11.

Thank you!

postgresql postgresql-performance postgresql-11

share|improve this question

asked 11 mins ago

Qiushi BaiQiushi Bai

83

/*+ IndexOnlyScan(a) */ explain analyze select ctid from a where id = 823977776533426178;

                                                QUERY PLAN                                                

----------------------------------------------------------------------------------------------------------

 Index Scan using a_pkey on a  (cost=0.14..8.16 rows=1 width=6) (actual time=0.038..0.039 rows=1 loops=1)

   Index Cond: (id = '823977776533426178'::bigint)

 Planning Time: 0.122 ms

 Execution Time: 0.095 ms

(4 rows)

share|improve this question

asked 11 mins ago

Qiushi BaiQiushi Bai

83

share|improve this question

asked 11 mins ago

Qiushi BaiQiushi Bai

83

asked 11 mins ago

Qiushi BaiQiushi Bai

83

asked 11 mins ago

Qiushi BaiQiushi Bai

83