Excellent idea there, xrg! Very cunning indeed! Blackadder himself would have been proud. I was keen to see if it would provide even greater performance gains due to decimating the number of SQL queries issued.
I'm having trouble making it fly though:
Code:
explain analyze select dialprefix from cc_ratecard where dialprefix = ANY(dial_exp_prefix('01392XXXXXX'));
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------
Seq Scan on cc_ratecard (cost=0.00..11783.67 rows=81209 width=9) (actual time=2032.716..2032.716 rows=0 loops=1)
Filter: (dialprefix = ANY (dial_exp_prefix('01392XXXXXX'::text)))
Total runtime: 2032.748 ms
Meh, it takes nearly 10 times longer than Areski's original query whilst not returning any results. It's still using a sequential scan, rather than an index scan. WTF is going on here?
Part of the problem is I can't get the function to return anything but an empty string:
Code:
select dial_exp_prefix('01392XXXXXX'::text);
dial_exp_prefix
-----------------
(1 row)
OK, no problem we'll prove the concept manually and worry about that later:
Code:
explain analyze select dialprefix from cc_ratecard where dialprefix = ANY(ARRAY['01392XXXXXX','01392XXXXX','01392XXXX','01392XXX',
'01392XX','01392X','01392','0139','013','01','0']) order by length(dialprefix) desc;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Sort (cost=19316.76..19519.78 rows=81209 width=9) (actual time=209.257..209.335 rows=49 loops=1)
Sort Key: length(dialprefix)
-> Seq Scan on cc_ratecard (cost=0.00..11580.65 rows=81209 width=9) (actual time=26.019..209.106 rows=49 loops=1)
Filter: (dialprefix = ANY ('{01392XXXXXX,01392XXXXX,01392XXXX,01392XXX,01392XX,01392X,01392,
0139,013,01,0}'::text[]))
Total runtime: 209.461 ms
That's better. It's now returning the correct set of results (rows=49), and it's around 10% quicker than Areski's query. It's still using a sequential scan though.
The only way I could successfully force it to use an index scan was like:
Code:
explain analyze select dialprefix from cc_ratecard where (dialprefix = '01392XXXXXX' OR dialprefix = '01392XXXXX'
OR dialprefix = '01392XXXX' OR dialprefix = '01392XXX' OR dialprefix = '01392XX' OR dialprefix = '01392X'
OR dialprefix = '01392' OR dialprefix = '0139' OR dialprefix = '013' OR dialprefix = '01'
OR dialprefix = '0') order by length(dialprefix) desc;
QUERY PLAN
----------------------------------------------------------------------------------
Sort (cost=692.75..693.15 rows=163 width=9) (actual time=0.645..0.728 rows=49 loops=1)
Sort Key: length(dialprefix)
-> Index Scan using ind_cc_ratecard_dialprefix, ind_cc_ratecard_dialprefix, ind_cc_ratecard_dialprefix,
ind_cc_ratecard_dialprefix, ind_cc_ratecard_dialprefix, ind_cc_ratecard_dialprefix, ind_cc_ratecard_dialprefix,
ind_cc_ratecard_dialprefix, ind_cc_ratecard_dialprefix, ind_cc_ratecard_dialprefix, ind_cc_ratecard_dialprefix on cc_ratecard
(cost=0.00..686.76 rows=163 width=9) (actual time=0.060..0.525 rows=49 loops=1)
Index Cond: ((dialprefix = '01392XXXXXX'::text) OR (dialprefix = '01392XXXXX'::text)
OR (dialprefix = '01392XXXX'::text) OR (dialprefix = '01392XXX'::text) OR (dialprefix = '01392XX'::text)
OR (dialprefix = '01392X'::text) OR (dialprefix = '01392'::text) OR (dialprefix = '0139'::text)
OR (dialprefix = '013'::text) OR (dialprefix = '01'::text) OR (dialprefix = '0'::text))
Total runtime: 0.887 ms
Excellent, now we've got the correct set of 49 results and due to the index scan it's the quickest query by far! Assuming MySQL sees similar improvements I think implenting this solution in PHP is the way to go, as it's more portable and doesn't add plpgsql to A2B's list of dependencies.
PS thanks again to Xrg for causing me to revisit this.