Incorrect behavior with CE and ORDER BY

Hello,

We have a problem with CE that I want to verify is either expected
behavior, a bug or something else :).

Yes constraint exclusion is on.

I have tried increasing the default_statistics_target (all the way 1000)
no change in behavior.

Query plan with ORDER BY:

Limit (cost=47110.19..47110.31 rows=50 width=8) (actual
time=6088.013..6088.269 rows=50 loops=1)
-> Sort (cost=47110.19..47943.14 rows=333179 width=8) (actual
time=6088.007..6088.104 rows=50 loops=1)
Sort Key: public.tbl_profile_search.pse_lastlogin
-> Result (cost=0.00..16547.78 rows=333179 width=8) (actual
time=0.020..4339.472 rows=334319 loops=1)
-> Append (cost=0.00..16547.78 rows=333179 width=8)
(actual time=0.016..3208.022 rows=334319 loops=1)
-> Seq Scan on tbl_profile_search
(cost=0.00..2.27 rows=1 width=8) (actual time=0.012..0.047 rows=2 loops=1)
Filter: (((pse_normalized_text)::text =
'1'::text) AND (pse_interest_type = 10))
-> Index Scan using index_pse_09_on_part_1 on
tbl_profile_search_interest_1 tbl_profile_search (cost=0.00..4.73
rows=1 width=8) (actual time=0.202..0.202 rows=0 loops=1)
Index Cond: ((pse_normalized_text)::text =
'1'::text)
Filter: (pse_interest_type = 10)
-> Bitmap Heap Scan on
tbl_profile_search_interest_10 tbl_profile_search
(cost=3579.12..16540.78 rows=333177 width=8) (actual
time=90.619..2116.224 rows=334317 loops=1)
Recheck Cond: ((pse_normalized_text)::text =
'1'::text)
Filter: (pse_interest_type = 10)
-> Bitmap Index Scan on
index_pse_09_on_part_10 (cost=0.00..3579.12 rows=333177 width=0)
(actual time=89.052..89.052 rows=340964 loops=1)
Index Cond:
((pse_normalized_text)::text = '1'::text)
Total runtime: 6103.190 ms

Same query, just removed ORDER BY:

-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=0.00..2.48 rows=50 width=4) (actual time=0.025..57.146
rows=50 loops=1)
-> Result (cost=0.00..16549.78 rows=333179 width=4) (actual
time=0.021..56.993 rows=50 loops=1)
-> Append (cost=0.00..16549.78 rows=333179 width=4) (actual
time=0.017..56.835 rows=50 loops=1)
-> Seq Scan on tbl_profile_search (cost=0.00..2.27
rows=1 width=4) (actual time=0.013..0.050 rows=2 loops=1)
Filter: (((pse_normalized_text)::text = '1'::text)
AND (pse_interest_type = 10))
-> Index Scan using index_pse_09_on_part_1 on
tbl_profile_search_interest_1 tbl_profile_search (cost=0.00..4.73
rows=1 width=4) (actual time=0.051..0.051 rows=0 loops=1)
Index Cond: ((pse_normalized_text)::text = '1'::text)
Filter: (pse_interest_type = 10)
-> Bitmap Heap Scan on tbl_profile_search_interest_10
tbl_profile_search (cost=3581.12..16542.78 rows=333177 width=4) (actual
time=56.481..56.573 rows=48 loops=1)
Recheck Cond: ((pse_normalized_text)::text = '1'::text)
Filter: (pse_interest_type = 10)
-> Bitmap Index Scan on index_pse_09_on_part_10
(cost=0.00..3581.12 rows=333177 width=0) (actual time=54.999..54.999
rows=341233 loops=1)
Index Cond: ((pse_normalized_text)::text =
'1'::text)
Total runtime: 57.396 ms

--

=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate