Re: Limit changes query plan

Gaetano Mendola <mendola(at)bigfoot(dot)com> writes:
> Gregory Stark wrote:
>> It's evidently guessing wrong about the limit being satisfied early. The
>> non-indexed restrictions might be pruning out a lot more records than the
>> planner expects. Or possibly the table is just full of dead records.

> Here the analyze result:

> explain analyze SELECT c.id, tsk, lir, nctr, nctn, ncts, rvel,ecp, pvcp, pvcc,pvcf,pvcl,ldcn,ogtd,sgti FROM t_OA_2_00_card c JOIN t_OA_2_00_dt dt ON (dt.card_id = c.id) WHERE ecp=18 AND _to >= 1500 AND _from <= 1550 ORDER BY nctr,nctn,ncts,rvel offset 0 limit 5;

> QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Limit (cost=0.00..2125.12 rows=5 width=90) (actual time=3399923.424..3399960.174 rows=5 loops=1)
> -> Nested Loop (cost=0.00..4470402.02 rows=10518 width=90) (actual time=3399923.420..3399960.156 rows=5 loops=1)
> -> Index Scan using i_oa_2_00_card_keys on t_oa_2_00_card c (cost=0.00..3927779.56 rows=101872 width=90) (actual time=3399892.632..3399896.773 rows=50 loops=1)
> Filter: (ecp = 18)
> -> Index Scan using i_oa_2_00_dt_for on t_oa_2_00_dt dt (cost=0.00..5.31 rows=1 width=8) (actual time=1.264..1.264 rows=0 loops=50)
> Index Cond: (dt.card_id = c.id)
> Filter: ((_to >= 1500) AND (_from <= 1550))
> Total runtime: 3399960.277 ms

It's guessing that there are 101872 rows altogether that have ecp = 18.
Is that about right? If not, raising the statistics target for the
table might fix the problem. If it is about right, then you may be
stuck --- the problem then could be that the rows with ecp=18 aren't
uniformly scattered in the i_oa_2_00_card_keys ordering, but are
clustered near the end.

Greg's comment about dead rows might be correct too --- the actual
runtime for the indexscan seems kinda high even if it is scanning most
of the table. Also, if this query is important enough, clustering
by that index would improve matters, at the cost of possibly slowing
down other queries that use other indexes.

regards, tom lane