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.78rows=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
solutionssince 1997            http://www.commandprompt.com/
 

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