Hi, Michal.
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
On Monday 25 October 2004 17:42, Zygmuntowicz Michal wrote:
> You should consider groups to be an optional feature.
> You are using it, but another person may not need it at all.
> It is important difference, as someone else might write a patch
> that works well for scenario that does not use groups
> and does not work well for groups.
> Would you be happy with such patch?;-)
>
> Anyway, the problem is solved in the cvs.
>
> ----- Original Message -----
> From: "Kompnet" <kompnet@xxxxxxxxx>
> Sent: Monday, October 25, 2004 3:35 PM
>
> > What does it mean "default tariff (not assigned to any group/account)"?
> > I meant the default tariff is the tariff with the lowest priority.
> > But it must be assigned to any groups.
> > If I really understand you, you mean if user havn't grpid in
> > voiptariffsel for
> > any destinations the tariff with grpid=NULL from voiptariff must be
> > applied
> > for these destinations?
> > Well, if you think this is really importante difference I'll try to fix
> > my query.
> >
> > On Monday 25 October 2004 14:04, Zygmuntowicz Michal wrote:
> >> Unfortunatelly, your patch does not work fine.
> >> It cannot find a default tariff (not assigned to any group/account)
> >> - did you really check it?
> >>
> >> ----- Original Message -----
> >> From: "Kompnet" <kompnet@xxxxxxxxx>
> >> Sent: Friday, October 22, 2004 9:03 AM
> >>
> >> > Michal, hi.
> >> > The new patch works fine with default and other groups.
> >> > In attachment you can find EXPLAIN ANALYZE output.
> >> > I hope you'll apply it to the CVS. I thought it was in CVS at least
> >> > 1.5 month
> >> > ago.
> >> > Igor.
> >> >
> >> > On Tuesday 31 August 2004 17:21, you wrote:
> >> >> And assign it to each new account... Not the best idea too.
> >> >> But your patch is very useful, so I will modify it a bit to work
> >> >> well with default tariffs too and apply it to the cvs.
> >> >>
> >> >> Did you check the query with EXPLAIN? Is it using indices
> >> >> in a correct way, so it is fast?
> >> >>
> >> >> ----- Original Message -----
> >> >> From: "Kompnet" <kompnet@xxxxxxxxx>
> >> >> Sent: Tuesday, August 31, 2004 3:11 PM
> >> >>
> >> >> > I've not checked it, but it (default tariff with no group/selector)
> >> >> > is
> >> >> > not necessary in my way: you can set 'default' group 1.
> >> >> >
> >> >> > On Tuesday 31 August 2004 16:52, you wrote:
> >> >> > > I've got the idea - it's good. But isn't your patch breaking
> >> >> > > tariff matching for default tariffs (with no group/selector)?
> >> >> > >
> >> >> > > ----- Original Message -----
> >> >> > > From: "Kompnet" <kompnet@xxxxxxxxx>
> >> >> > > To: <openh323gk-users@xxxxxxxxxxxxxxxxxxxxx>; "Zygmuntowicz
> >> >> > > Michal"
> >> >> > > <m.zygmuntowicz@xxxxxxx> Sent: Tuesday, August 31, 2004 1:51 PM
> >> >> > > Subject: Changes in match_tariff function
> >> >> > > (sqlbill)
> >> >> > >
> >> >> > > > Hi, Michal.
> >> >> > > > I've changed you match_tariff function a little.
> >> >> > > > If it's needed to apply this schema:
> >> >> > > > the 1-st user must can place calls whit prefix 1234# with
> >> >> > > > common price, for the 2-nd user tariffs must be different for
> >> >> > > > different destinations, but he use 1234# prefix too,
> >> >> > > > and we use your variance of match_tariff, we need to write all
> >> >> > > > destinations for both users:
> >> >> > > > user1 1234#1 price1
> >> >> > > > user2 1234#1 price2
> >> >> > > > user1 1234#2 price1
> >> >> > > > user2 1234#2 price3
> >> >> > > > user1 1234#3 price1
> >> >> > > > user2 1234#3 price4
> >> >> > > > ...
> >> >> > > > In my variance we can to write destinations separately for each
> >> >> > > > user:
> >> >> > > > user1 1234# price1
> >> >> > > > user2 1234#1 price2
> >> >> > > > user2 1234#2 price3
> >> >> > > > user2 1234#2 price4
> >> >> > > > .....
> >> >> > > > --
> >> >> > > > Best regards,
> >> >> > > > Igor Prokhorov
>
> -------------------------------------------------------
> This SF.net email is sponsored by: IT Product Guide on ITManagersJournal
> Use IT products in your business? Tell us what you think of them. Give us
> Your Opinions, Get Free ThinkGeek Gift Certificates! Click to find out more
> http://productguide.itmanagersjournal.com/guidepromo.tmpl
>
> _______________________________________________________
>
> List: Openh323gk-users@xxxxxxxxxxxxxxxxxxxxx
> Archive: http://sourceforge.net/mailarchive/forum.php?forum_id=8549
> Homepage: http://www.gnugk.org/
--
Best regards,
Igor Prokhorov
-------------------------------------------------------
This SF.net email is sponsored by: IT Product Guide on ITManagersJournal
Use IT products in your business? Tell us what you think of them. Give us
Your Opinions, Get Free ThinkGeek Gift Certificates! Click to find out more
http://productguide.itmanagersjournal.com/guidepromo.tmpl
_______________________________________________________
List: Openh323gk-users@xxxxxxxxxxxxxxxxxxxxx
Archive: http://sourceforge.net/mailarchive/forum.php?forum_id=8549
Homepage: http://www.gnugk.org/
