Select performance variation based on the different combinations of using where lower(), order by, and limit

From: Tyler Reese <jukey91(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Select performance variation based on the different combinations of using where lower(), order by, and limit
Date: 2013-08-16 22:44:33
Message-ID: CAFvRLyfK5oFYWQSmhhrqF4_m9s+WOBirEBc9mBTwLEJMcreF6g@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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)

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2013-08-16 23:28:29 Re: Using an index to materialize a function
Previous Message Kevin Grittner 2013-08-16 21:46:55 Re: Using an index to materialize a function