I have a table that has around 8 million rows. The table has 71 columns and 33 indexes.
The relevant indexes are:
"callingpartynumber" btree ("CallingPartyNumber")
"callingpartynumber_lower" btree (lower("CallingPartyNumber") text_pattern_ops)
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)
