If you find a way to force the planner to execute currency filter after prefix filter, let me know. I did a few attempts, but somehow the planner always chooses currency filter to be executed first:( And this gives us bug performance penalty (the query is 3-4 times slower). Maybe creating an index over currencysym will make the planner smarter, but... maybe there is a simplier way.
----- Original Message ----- From: "Kompnet" <kompnet@xxxxxxxxx>
Sent: Tuesday, October 26, 2004 9:04 AM
I wuldn't be happy :-((.
In your query order of ORDERs changed, and as result not the tariff with
highest priority matches but with longest preffix.
Must be "....ORDER BY voiptariffgrp.priority DESC,
length(voiptariffdst.prefix) DESC LIMIT 1;"
or in your terms
".... ORDER BY G.priority DESC, length(D.prefix) DESC LIMIT 1;".
And I think the rules for the structure of tables must be more strong.
Is it really necessary to create one group with different currencysym-s for
the same destination? Only introduction of strict conformity " one group -
one currency " allows to reduce time of processing of query up to four times:
voipdb=> explain analyze SELECT voiptariffdst.id, voiptariffdst.active,
voiptariff.id FROM voiptariffdst LEFT JOIN voiptariff ON voiptariff.dstid =
voiptariffdst.id LEFT JOIN voiptariffgrp ON voiptariff.grpid =
voiptariffgrp.id LEFT JOIN voiptariffsel ON voiptariffsel.grpid =
voiptariffgrp.id WHERE NOT voiptariffdst.exactmatch AND ('0022#70963588998'
LIKE (voiptariffdst.prefix || '%')) AND NOT voiptariff.terminating AND
voiptariff.currencysym = 'RUB' AND (voiptariff.grpid IS NULL OR
voiptariffsel.accountid = 2) ORDER BY voiptariffgrp.priority DESC,
length(voiptariffdst.prefix) DESC LIMIT 1;
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=76.16..76.16 rows=1 width=45) (actual time=22.762..22.764 rows=1
loops=1)
-> Sort (cost=76.16..76.16 rows=2 width=45) (actual time=22.755..22.755
rows=1 loops=1)
Sort Key: voiptariffgrp.priority, length(voiptariffdst.prefix)
-> Nested Loop Left Join (cost=0.00..76.15 rows=2 width=45) (actual
time=0.490..22.703 rows=2 loops=1)
Filter: (("outer".grpid IS NULL) OR ("inner".accountid = 2))
-> Nested Loop Left Join (cost=0.00..41.86 rows=2 width=53)
(actual time=0.436..22.603 rows=2 loops=1)
-> Nested Loop (cost=0.00..37.02 rows=1 width=45)
(actual time=0.410..22.539 rows=2 loops=1)
-> Seq Scan on voiptariff (cost=0.00..22.50
rows=3 width=12) (actual time=0.046..5.757 rows=855 loops=1)
Filter: ((NOT terminating) AND (currencysym =
'RUB'::bpchar))
-> Index Scan using voiptariffdst_pkey on
voiptariffdst (cost=0.00..4.83 rows=1 width=37) (actual time=0.015..0.015
rows=0 loops=855)
Index Cond: ("outer".dstid =
voiptariffdst.id)
Filter: ((NOT exactmatch) AND
('0022#70963588998'::text ~~ (prefix || '%'::text)))
-> Index Scan using voiptariffgrp_pkey on voiptariffgrp
(cost=0.00..4.82 rows=1 width=8) (actual time=0.013..0.017 rows=1 loops=2)
Index Cond: ("outer".grpid = voiptariffgrp.id)
-> Index Scan using voiptariffsel_unique on voiptariffsel
(cost=0.00..17.07 rows=5 width=8) (actual time=0.012..0.022 rows=2 loops=2)
Index Cond: (voiptariffsel.grpid = "outer".id)
Total runtime: 23.083 ms
______
voipdb=> explain analyze SELECT voiptariffdst.id, voiptariffdst.active,
voiptariff.id FROM voiptariffdst LEFT JOIN voiptariff ON voiptariff.dstid =
voiptariffdst.id LEFT JOIN voiptariffgrp ON voiptariff.grpid =
voiptariffgrp.id LEFT JOIN voiptariffsel ON voiptariffsel.grpid =
voiptariffgrp.id WHERE NOT voiptariffdst.exactmatch AND ('0022#70963588998'
LIKE (voiptariffdst.prefix || '%')) AND NOT voiptariff.terminating AND
(voiptariff.grpid IS NULL OR voiptariffsel.accountid = 2) ORDER BY
voiptariffgrp.priority DESC, length(voiptariffdst.prefix) DESC LIMIT 1;
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Limit (cost=108.66..108.66 rows=1 width=45) (actual time=4.881..4.883 rows=1
loops=1)
-> Sort (cost=108.66..108.66 rows=3 width=45) (actual time=4.875..4.875
rows=1 loops=1)
Sort Key: voiptariffgrp.priority, length(voiptariffdst.prefix)
-> Nested Loop Left Join (cost=25.01..108.63 rows=3 width=45)
(actual time=1.754..4.831 rows=2 loops=1)
Filter: (("outer".grpid IS NULL) OR ("inner".accountid = 2))
-> Nested Loop Left Join (cost=25.01..57.20 rows=3 width=53)
(actual time=1.703..4.732 rows=2 loops=1)
-> Hash Join (cost=25.01..47.53 rows=2 width=45)
(actual time=1.661..4.639 rows=2 loops=1)
Hash Cond: ("outer".dstid = "inner".id)
-> Seq Scan on voiptariff (cost=0.00..20.00
rows=500 width=12) (actual time=0.008..1.811 rows=855 loops=1)
Filter: (NOT terminating)
-> Hash (cost=25.00..25.00 rows=3 width=37)
(actual time=1.581..1.581 rows=0 loops=1)
-> Seq Scan on voiptariffdst
(cost=0.00..25.00 rows=3 width=37) (actual time=0.076..1.568 rows=2 loops=1)
Filter: ((NOT exactmatch) AND
('0022#70963588998'::text ~~ (prefix || '%'::text)))
-> Index Scan using voiptariffgrp_pkey on voiptariffgrp
(cost=0.00..4.82 rows=1 width=8) (actual time=0.025..0.028 rows=1 loops=2)
Index Cond: ("outer".grpid = voiptariffgrp.id)
-> Index Scan using voiptariffsel_unique on voiptariffsel
(cost=0.00..17.07 rows=5 width=8) (actual time=0.012..0.021 rows=2 loops=2)
Index Cond: (voiptariffsel.grpid = "outer".id)
Total runtime: 5.196 ms
------------------------------------------------------- This SF.Net email is sponsored by: Sybase ASE Linux Express Edition - download now for FREE LinuxWorld Reader's Choice Award Winner for best database on Linux. http://ads.osdn.com/?ad_id=5588&alloc_id=12065&op=click
_______________________________________________________
List: Openh323gk-users@xxxxxxxxxxxxxxxxxxxxx Archive: http://sourceforge.net/mailarchive/forum.php?forum_id=8549 Homepage: http://www.gnugk.org/
