Re: LIMIT confuses the planner

Now I am experiencing similar issue with another table, called
"message", for which there's a conditional index:

CREATE TABLE message (
message_sid SERIAL PRIMARY KEY,
from_profile_sid INT NOT NULL REFERENCES profile,
to_profile_sid INT NOT NULL REFERENCES profile,
sender_has_deleted BOOLEAN NOT NULL DEFAULT FALSE,
receiver_has_deleted BOOLEAN NOT NULL DEFAULT FALSE,
datetime TIMESTAMP NOT NULL DEFAULT NOW(),
body TEXT
);

CREATE INDEX message_from_profile_idx (from_profile_sid) WHERE NOT
sender_has_deleted;

So, again... adding a LIMIT makes the planner choose the "wrong" index.

db=# EXPLAIN ANALYZE SELECT
message_sid
FROM
message
WHERE
from_profile_sid = 1296 AND NOT sender_has_deleted
ORDER BY
message_sid DESC;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=2307.70..2310.74 rows=1215 width=4) (actual
time=0.040..0.040 rows=2 loops=1)
Sort Key: message_sid
Sort Method: quicksort Memory: 25kB
-> Bitmap Heap Scan on message (cost=23.59..2245.45 rows=1215
width=4) (actual time=0.029..0.033 rows=2 loops=1)
Recheck Cond: ((from_profile_sid = 1296) AND (NOT
sender_has_deleted))
-> Bitmap Index Scan on message_from_profile_idx
(cost=0.00..23.28 rows=1215 width=0) (actual time=0.022..0.022 rows=2
loops=1)
Index Cond: (from_profile_sid = 1296)
Total runtime: 0.068 ms
(8 rows)

db=# EXPLAIN ANALYZE SELECT
message_sid
FROM
message
WHERE
from_profile_sid = 1296 AND NOT sender_has_deleted
ORDER BY
message_sid DESC LIMIT 20;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..1461.12 rows=20 width=4) (actual
time=0.817..932.398 rows=2 loops=1)
-> Index Scan Backward using message_pkey on message
(cost=0.00..88762.80 rows=1215 width=4) (actual time=0.816..932.395
rows=2 loops=1)
Filter: ((NOT sender_has_deleted) AND (from_profile_sid = 1296))
Total runtime: 932.432 ms
(4 rows)

I had already increased STATISTICS to 1000 for both from_profile_sid and
sender_has_deleted, and vacuum analyzed respectively (also did reindex),
but still statistical data is confusing me:

db=# SELECT n_distinct FROM pg_stats WHERE tablename='message' AND
attname='from_profile_sid';

n_distinct
------------
4068
(1 row)

db=# select avg(length) from (select from_profile_sid, count(*) as
length from message group by from_profile_sid) as freq;

avg
----------------------
206.5117822008693663
(1 row)

Any ideas/thoughts?

--
Kouber Saparev
http://kouber.saparev.com