Select performance variation based on the different combinations of using where lower(), order by, and limit - Mailing list pgsql-general

I have a table that has around 8 million rows.  The table has 71 columns and 33 indexes.

The relevant indexes are:

My question is about the explain analyze output, below.  In my desired query, I want to use (1) a where clause (with a call to lower() on the column name), (2) order by, and (3) limit.  When I use all three, my query is slow, but if i use 2 of the 3 clauses, the query is fast.

case 1: where clause with lower(), and order by

case 2: where clause without lower(), order by, and limit

case 3: where clause with lower(), and limit

case 4: where clause with lower(), order by, and limit

I don't understand why the performance of case 4 is so much slower than the other three cases.  It isn't using the callingpartynumber_lower index, when the only difference between case 4 and case 1 is the limit 100 clause.  If I were to use limit 1, there is no difference.

case 1:

mydb=> explain analyze SELECT * FROM "cdr" WHERE lower("CallingPartyNumber") = '9725551212' order by "key";

                                                                  QUERY PLAN                                                                  

----------------------------------------------------------------------------------------------------------------------------------------------

 Sort  (cost=128701.67..128804.70 rows=41212 width=757) (actual time=0.425..0.495 rows=11 loops=1)

   Sort Key: key

   Sort Method:  quicksort  Memory: 30kB

   ->  Bitmap Heap Scan on cdr  (cost=916.83..111735.11 rows=41212 width=757) (actual time=0.118..0.246 rows=11 loops=1)

         Recheck Cond: (lower("CallingPartyNumber") = '9725551212'::text)

         ->  Bitmap Index Scan on callingpartynumber_lower  (cost=0.00..906.53 rows=41212 width=0) (actual time=0.083..0.083 rows=11 loops=1)

               Index Cond: (lower("CallingPartyNumber") = '9725551212'::text)

 Total runtime: 0.830 ms

(8 rows)

case 2:

mydb=> explain analyze SELECT * FROM "cdr" WHERE "CallingPartyNumber" = '9725551212' order by "key" limit 100;

                                                                QUERY PLAN                                                                

------------------------------------------------------------------------------------------------------------------------------------------

 Limit  (cost=958.12..958.37 rows=100 width=757) (actual time=3.741..4.035 rows=11 loops=1)

   ->  Sort  (cost=958.12..958.72 rows=240 width=757) (actual time=3.723..3.834 rows=11 loops=1)

         Sort Key: key

         Sort Method:  quicksort  Memory: 30kB

         ->  Bitmap Heap Scan on cdr  (cost=7.30..948.94 rows=240 width=757) (actual time=3.425..3.553 rows=11 loops=1)

               Recheck Cond: ("CallingPartyNumber" = '9725551212'::text)

               ->  Bitmap Index Scan on callingpartynumber  (cost=0.00..7.24 rows=240 width=0) (actual time=3.385..3.385 rows=11 loops=1)

                     Index Cond: ("CallingPartyNumber" = '9725551212'::text)

 Total runtime: 4.550 ms

(9 rows)

case 3:

mydb=> explain analyze SELECT * FROM "cdr" WHERE lower("CallingPartyNumber") = '9725551212' limit 100;

                                                                   QUERY PLAN                                                                 

  

------------------------------------------------------------------------------------------------------------------------------------------------

 Limit  (cost=0.00..385.54 rows=100 width=757) (actual time=0.079..0.343 rows=11 loops=1)

   ->  Index Scan using callingpartynumber_lower on cdr  (cost=0.00..158886.65 rows=41212 width=757) (actual time=0.059..0.177 rows=11 loops=1)

         Index Cond: (lower("CallingPartyNumber") = '9725551212'::text)

 Total runtime: 0.687 ms

(4 rows)

case 4:

mydb=> explain analyze SELECT * FROM "cdr" WHERE lower("CallingPartyNumber") = '9725551212' order by "key" limit 100;

                                                                QUERY PLAN                                                                

------------------------------------------------------------------------------------------------------------------------------------------

 Limit  (cost=0.00..72882.05 rows=100 width=757) (actual time=20481.083..30464.960 rows=11 loops=1)

   ->  Index Scan using cdr_pkey on cdr  (cost=0.00..30036152.32 rows=41212 width=757) (actual time=20481.049..30464.686 rows=11 loops=1)

         Filter: (lower("CallingPartyNumber") = '9725551212'::text)

 Total runtime: 30465.246 ms

(4 rows)