Fabrício dos Anjos Silva<fabricio(dot)silva(at)linkcom(dot)com(dot)br> wrote:
> explain analyze select max(cnpj) from empresa where dtcriacao >=
> current_date-5;
> Result (cost=32.24..32.24 rows=1 width=0) (actual
> time=5223.937..5223.938 rows=1 loops=1)
> InitPlan 1 (returns $0)
> -> Limit (cost=0.00..32.24 rows=1 width=15) (actual
> time=5223.921..5223.922 rows=1 loops=1)
> -> Index Scan Backward using idx_cnpj on empresa
> (cost=0.00..65925.02 rows=2045 width=15) (actual
> time=5223.913..5223.913 rows=1 loops=1)
> Index Cond: ((cnpj)::text IS NOT NULL)
> Filter: (dtcriacao >= (('now'::text)::date - 5))
> Total runtime: 5224.037 ms
> My question is: Why the cost of Limit on the last query, estimated
> as 32.24 if the Index Scan Backward is estimated at 65925.02?
If you divide the total cost for the step by the number of rows it
would take to read all the way through, you get 32.24; so it clearly
expects to find a row which matches the filter condition right away.
(Or it fails to consider the fact that the filter condition could
cause it to read multiple rows looking for a match.)
> Since there is a filter based on column dtcriacao, the whole index
> is going to be analyzed, and Limit is going to wait for the
> complete Index Scan to complete.
Only if there are no matching rows. Since you're asking for the
max, if it reads in descending sequence on the index, it can stop as
soon as it finds one matching row.
-Kevin