sql - Understanding finding max value with index in postgreSQL -
i have following table:
create table foo ( c1 integer, c2 text )
created follows:
create indedx on foo(); insert foo select i, md5(random()::text) generate_series(1, 1000000) i; analyze baz;
now, tried query:
explain (analyze, buffers) select max(c2) foo;
and got following plan:
result (cost=0.08..0.09 rows=1 width=0) (actual time=0.574..0.574 rows=1 loops=1) buffers: shared read=5 initplan 1 (returns $0) -> limit (cost=0.00..0.08 rows=1 width=33) (actual time=0.570..0.571 rows=1 loops=1) buffers: shared read=5 -> index scan backward using foo_c2_idx on foo (cost=0.00..79676.27 rows=1000000 width=33) (actual time=0.569..0.569 rows=1 loops=1) index cond: (c2 not null) heap fetches: 1 buffers: shared read=5
what confused resulting cost 0.08.. 0.09
. why?
i thought find max
, if had index on column had perform index scan
, read @ least 1 of index leafs. reading leafs in turn accomplished 1 random acces costs 4. so, cost should have been more 4.
what did miss here?
the cost of index scan pro-rated limit. proration logic not try take rounding of page accesses integers account, @ point proration done has been collapsed single floating point number.
Comments
Post a Comment