#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    5
    Rep Power
    0

    The problem with the speed during the search of hotels


    The project is developed on PHP + PostgreSQL. The database of the project is quite large (1,2 GB), the fields are indexed.

    How are the prices stored?
    Prices for hotels are stored in the database according to the periods of time, for example:
    from 01.01.2013 to 01.02.2013 ( for days of the week Mon, Tue, Wed , Thu ) the price is set at $ 10 (for one day of this period ) for the price profile VIP1,
    $ 20 for the price profile VIP2;
    from 01.01.2013 to 01.02.2013 ( for days of the week Fri , Sat, Sun) the price is set at $ 5 ( for one day of this period ) for the price profile VIP1 and $ 10 for the price profile VIP2 respectively.
    Each user is assigned to the individual price profile.
    During the search of hotels the system counts prices (this is performed on the database level) according to the price profile and the period of time. After that some manipulations with the data on the PHP level are performed. The speed of work during these operations is very low. Is there any possibility to optimize the process of searching?
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Loyal (3000 - 3499 posts)

    Join Date
    Jul 2003
    Posts
    3,457
    Rep Power
    594
    This is probably not a PHP issue but rather a PostgreSQL issue. It is my guess (not a DBA) that you need to add indices to your DB on those fields. Ask a mod to move this to the PostgreSQL forum by clicking on the red triangle in the upper right of your post.
    There are 10 kinds of people in the world. Those that understand binary and those that don't.
  4. #3
  5. Sarcky
    Devshed Supreme Being (6500+ posts)

    Join Date
    Oct 2006
    Location
    Pennsylvania, USA
    Posts
    10,907
    Rep Power
    6351
    welcome to the postgres forum.
    HEY! YOU! Read the New User Guide and Forum Rules

    "They that can give up essential liberty to obtain a little temporary safety deserve neither liberty nor safety." -Benjamin Franklin

    "The greatest tragedy of this changing society is that people who never knew what it was like before will simply assume that this is the way things are supposed to be." -2600 Magazine, Fall 2002

    Think we're being rude? Maybe you asked a bad question or you're a Help Vampire. Trying to argue intelligently? Please read this.
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    indexing is an art, you cannot just "index the tables" becaus that will not help.


    Qhat kind of queries are you running, have you determined which of the queries (if any) is the problem?
    And what is "slow" exactly? Are you looking at a five second query or 20ms?
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,784
    Rep Power
    348
    Originally Posted by d.tereschuk
    The speed of work during these operations is very low. Is there any possibility to optimize the process of searching?
    Please read this: https://wiki.postgresql.org/wiki/Slow_Query_Questions and then add the necessary information

    Comments on this post

    • ManiacDan agrees
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags.
    http://forums.devshed.com/misc.php?do=bbcode#code

    Tips on how to ask better questions:
    http://tkyte.blogspot.de/2005/06/how-to-ask-questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    5
    Rep Power
    0
    My Query
    WITH tm AS (
    SELECT
    nl_price_catalog.id_price_catalog,
    nl_room.id_hotel,
    nl_hotel.id_hotel_class,
    nl_price_catalog.id_room,
    nl_price_catalog.id_placement, nl_price_catalog.id_food_type, nl_price_catalog.id_price_profile,
    room_price_sum('2013-12-23', '2013-12-29', nl_price_catalog.id_room, nl_price_catalog.id_placement, nl_price_catalog.id_food_type, 18, nl_price_catalog.id_price_profile, 0, 22) as price,
    nl_placement.num_adult, nl_placement.num_children, nl_hotel.use_float_rr,
    nl_currency.name as currency, 0 as id_room_action_tariff, 0 as id_action_tariff

    FROM
    nl_price_catalog, nl_period, nl_placement, nl_room, nl_city, nl_region, nl_country, nl_currency, nl_hotel

    WHERE
    nl_price_catalog.status IN (1, 2, 5)
    AND nl_price_catalog.id_currency = 18
    AND nl_price_catalog.id_price_profile =get_price_profile_id_room_right(22,nl_region.id_country,nl_city.id_region,nl_hotel.id_city,nl_room. id_hotel,nl_price_catalog.id_room,nl_price_catalog.id_food_type,nl_price_catalog.id_placement, 1)
    AND nl_period.active = true
    AND (('2013-12-23'>= nl_period.date_begin AND '2013-12-29' >= nl_period.date_end AND '2013-12-23' <= nl_period.date_end) OR
    ('2013-12-23'<= nl_period.date_begin AND '2013-12-29' >= nl_period.date_end) OR
    ('2013-12-23'<= nl_period.date_begin AND '2013-12-29' <= nl_period.date_end AND '2013-12-29' >= nl_period.date_begin) OR
    ('2013-12-23'>= nl_period.date_begin AND '2013-12-29' <= nl_period.date_end))
    AND nl_period.id_period = nl_price_catalog.id_period
    AND nl_placement.id_placement = nl_price_catalog.id_placement
    AND nl_room.id_room = nl_price_catalog.id_room
    AND nl_room.active = true
    AND nl_hotel.active = true
    AND nl_city.active = true
    AND nl_hotel.id_hotel = nl_room.id_hotel
    AND nl_city.id_city = nl_hotel.id_city
    AND nl_region.id_region = nl_city.id_region
    AND nl_country.id_country = nl_region.id_country
    AND nl_price_catalog.id_currency = nl_currency.id_currency
    AND (nl_placement.num_adult >= 1) AND nl_hotel.id_city = 29
    AND nl_hotel.id_hotel IN(SELECT COALESCE(nl_hotel.id_hotel, 0) FROM nl_hotel LEFT JOIN nl_hotel_distance ON nl_hotel_distance.id_hotel = nl_hotel.id_hotel WHERE active = true AND nl_hotel.id_city = 29 AND ((nl_hotel_distance.distance <= 10) OR (nl_hotel_distance.distance >= 10)) AND nl_hotel_distance.id_keypoint = 1 ORDER BY popularity DESC)
    )

    SELECT tm.id_hotel,
    tm.id_hotel_class,
    tm.id_room,
    tm.id_placement,
    tm.id_food_type,
    tm.id_price_profile,
    tm.price,
    tm.num_adult,
    tm.num_children,
    tm.use_float_rr,
    tm.currency,
    0 as id_room_action_tariff,
    0 as id_action_tariff,
    count
    FROM (SELECT tm.id_hotel, tm.price, MIN(tm.id_price_catalog) as id_price_catalog
    FROM
    (SELECT tm.id_hotel, MIN(tm.price) as price

    FROM tm

    GROUP BY tm.id_hotel
    ) min_price

    LEFT JOIN tm ON (tm.id_hotel = min_price.id_hotel AND tm.price = min_price.price)

    GROUP BY tm.id_hotel, tm.price
    ) result_price
    LEFT JOIN tm ON tm.id_price_catalog = result_price.id_price_catalog
    LEFT JOIN (SELECT tm.id_hotel, COUNT(DISTINCT(tm.id_room))-1 as count FROM tm GROUP BY tm.id_hotel) count_room ON count_room.id_hotel = result_price.id_hotel
    GROUP BY tm.id_hotel,
    tm.id_hotel_class,
    tm.id_room,
    tm.id_placement,
    tm.id_food_type,
    tm.id_price_profile,
    tm.price,
    tm.num_adult,
    tm.num_children,
    tm.use_float_rr,
    tm.currency,
    id_room_action_tariff,
    id_action_tariff,
    count
    HAVING tm.price > 0

    QUERY PLAN
    "HashAggregate (cost=7960.21..7960.22 rows=1 width=111) (actual time=1371.854..1371.865 rows=63 loops=1)"
    " CTE tm"
    " -> Hash Semi Join (cost=449.81..7959.95 rows=1 width=37) (actual time=7.326..1263.648 rows=1719 loops=1)"
    " Hash Cond: (public.nl_hotel.id_hotel = "ANY_subquery"."coalesce")"
    " -> Nested Loop (cost=360.38..7870.21 rows=16 width=41) (actual time=5.785..253.603 rows=1719 loops=1)"
    " -> Seq Scan on nl_currency (cost=0.00..1.07 rows=1 width=8) (actual time=0.003..0.004 rows=1 loops=1)"
    " Filter: (id_currency = 18)"
    " -> Hash Join (cost=360.38..7868.98 rows=16 width=41) (actual time=5.780..253.107 rows=1719 loops=1)"
    " Hash Cond: (nl_price_catalog.id_placement = nl_placement.id_placement)"
    " -> Nested Loop (cost=359.23..7867.61 rows=16 width=37) (actual time=5.763..251.502 rows=1719 loops=1)"
    " Join Filter: (nl_price_catalog.id_price_profile = get_price_profile_id_room_right(22, nl_region.id_country, nl_city.id_region, public.nl_hotel.id_city, nl_room.id_hotel, nl_price_catalog.id_room, nl_price_catalog.id_food_type, nl_price_catalog.id_placement, 1))"
    " -> Nested Loop (cost=0.00..16.35 rows=1 width=12) (actual time=0.105..0.112 rows=1 loops=1)"
    " -> Nested Loop (cost=0.00..16.01 rows=1 width=12) (actual time=0.099..0.104 rows=1 loops=1)"
    " Join Filter: (nl_city.id_region = nl_region.id_region)"
    " -> Index Scan using prime_id_city on nl_city (cost=0.00..8.27 rows=1 width=8) (actual time=0.012..0.012 rows=1 loops=1)"
    " Index Cond: (id_city = 29)"
    " Filter: active"
    " -> Seq Scan on nl_region (cost=0.00..4.55 rows=255 width=8) (actual time=0.002..0.052 rows=255 loops=1)"
    " -> Index Scan using prime_id_country on nl_country (cost=0.00..0.33 rows=1 width=4) (actual time=0.004..0.005 rows=1 loops=1)"
    " Index Cond: (nl_country.id_country = nl_region.id_country)"
    " -> Hash Join (cost=359.23..6998.66 rows=3248 width=41) (actual time=5.559..67.797 rows=6803 loops=1)"
    " Hash Cond: (nl_price_catalog.id_room = nl_room.id_room)"
    " -> Hash Join (cost=296.67..6851.74 rows=13836 width=24) (actual time=4.335..61.565 rows=15511 loops=1)"
    " Hash Cond: (nl_price_catalog.id_period = nl_period.id_period)"
    " -> Seq Scan on nl_price_catalog (cost=0.00..6131.82 rows=75971 width=28) (actual time=0.008..43.044 rows=75460 loops=1)"
    " Filter: ((id_currency = 18) AND (status = ANY ('{1,2,5}'::integer[])))"
    " -> Hash (cost=282.58..282.58 rows=1127 width=4) (actual time=4.297..4.297 rows=1235 loops=1)"
    " -> Seq Scan on nl_period (cost=0.00..282.58 rows=1127 width=4) (actual time=0.038..3.958 rows=1235 loops=1)"
    " Filter: (active AND ((('2013-12-23'::date >= date_begin) AND ('2013-12-29'::date >= date_end) AND ('2013-12-23'::date <= date_end)) OR (('2013-12-23'::date <= date_begin) AND ('2013-12-29'::date >= date_end)) OR (('2013-12-23'::date <= date_begin) AND ('2013-12-29'::date <= date_end) AND ('2013-12-29'::date >= date_begin)) OR (('2013-12-23'::date >= date_begin) AND ('2013-12-29'::date <= date_end))))"
    " -> Hash (cost=57.34..57.34 rows=418 width=21) (actual time=1.215..1.215 rows=409 loops=1)"
    " -> Hash Join (cost=13.91..57.34 rows=418 width=21) (actual time=0.179..1.057 rows=409 loops=1)"
    " Hash Cond: (nl_room.id_hotel = public.nl_hotel.id_hotel)"
    " -> Seq Scan on nl_room (cost=0.00..32.81 rows=1716 width=8) (actual time=0.005..0.415 rows=1716 loops=1)"
    " Filter: active"
    " -> Hash (cost=12.95..12.95 rows=77 width=13) (actual time=0.167..0.167 rows=77 loops=1)"
    " -> Seq Scan on nl_hotel (cost=0.00..12.95 rows=77 width=13) (actual time=0.004..0.139 rows=77 loops=1)"
    " Filter: (active AND (id_city = 29))"
    " -> Hash (cost=1.07..1.07 rows=6 width=8) (actual time=0.008..0.008 rows=6 loops=1)"
    " -> Seq Scan on nl_placement (cost=0.00..1.07 rows=6 width=8) (actual time=0.002..0.005 rows=6 loops=1)"
    " Filter: (num_adult >= 1)"
    " -> Hash (cost=89.18..89.18 rows=20 width=4) (actual time=0.898..0.898 rows=77 loops=1)"
    " -> Subquery Scan "ANY_subquery" (cost=88.93..89.18 rows=20 width=4) (actual time=0.847..0.876 rows=77 loops=1)"
    " -> Sort (cost=88.93..88.98 rows=20 width=8) (actual time=0.846..0.855 rows=77 loops=1)"
    " Sort Key: public.nl_hotel.popularity"
    " Sort Method: quicksort Memory: 28kB"
    " -> Hash Join (cost=13.91..88.50 rows=20 width=8) (actual time=0.201..0.797 rows=77 loops=1)"
    " Hash Cond: (nl_hotel_distance.id_hotel = public.nl_hotel.id_hotel)"
    " -> Seq Scan on nl_hotel_distance (cost=0.00..74.07 rows=84 width=4) (actual time=0.011..0.570 rows=84 loops=1)"
    " Filter: ((id_keypoint = 1) AND ((distance <= 10) OR (distance >= 10)))"
    " -> Hash (cost=12.95..12.95 rows=77 width=8) (actual time=0.182..0.182 rows=77 loops=1)"
    " -> Seq Scan on nl_hotel (cost=0.00..12.95 rows=77 width=8) (actual time=0.009..0.154 rows=77 loops=1)"
    " Filter: (active AND (id_city = 29))"
    " -> Nested Loop (cost=0.12..0.23 rows=1 width=111) (actual time=1294.536..1371.774 rows=63 loops=1)"
    " Join Filter: ((min(tm.id_price_catalog)) = tm.id_price_catalog)"
    " -> Nested Loop Left Join (cost=0.12..0.19 rows=1 width=12) (actual time=1294.132..1335.381 rows=63 loops=1)"
    " Join Filter: (tm.id_hotel = tm.id_hotel)"
    " -> HashAggregate (cost=0.09..0.10 rows=1 width=40) (actual time=1292.832..1292.846 rows=63 loops=1)"
    " -> Nested Loop Left Join (cost=0.02..0.08 rows=1 width=40) (actual time=1268.587..1292.747 rows=158 loops=1)"
    " Join Filter: ((tm.id_hotel = tm.id_hotel) AND (tm.price = (min(tm.price))))"
    " -> HashAggregate (cost=0.02..0.04 rows=1 width=36) (actual time=1268.563..1268.581 rows=63 loops=1)"
    " -> CTE Scan on tm (cost=0.00..0.02 rows=1 width=36) (actual time=7.330..1266.426 rows=1719 loops=1)"
    " -> CTE Scan on tm (cost=0.00..0.02 rows=1 width=40) (actual time=0.000..0.183 rows=1719 loops=63)"
    " -> GroupAggregate (cost=0.03..0.06 rows=1 width=8) (actual time=0.023..0.668 rows=63 loops=63)"
    " -> Sort (cost=0.03..0.04 rows=1 width=8) (actual time=0.011..0.126 rows=1719 loops=63)"
    " Sort Key: tm.id_hotel"
    " Sort Method: quicksort Memory: 129kB"
    " -> CTE Scan on tm (cost=0.00..0.02 rows=1 width=8) (actual time=0.001..0.274 rows=1719 loops=1)"
    " -> CTE Scan on tm (cost=0.00..0.02 rows=1 width=107) (actual time=0.001..0.410 rows=1719 loops=63)"
    " Filter: (tm.price > 0::numeric)"
    "Total runtime: 1372.226 ms"

    On search period one day my query running 1.3 second, if period 1 month 5 seconds.

    My functions

    count_same_week_day(self character varying, date_begin date, date_end date, period_begin date, period_end date)
    RETURNS integer AS
    $BODY$
    DECLARE
    count int;
    week_day int[];
    i int;
    start_day date;
    current_day int;
    BEGIN

    select INTO count 0;
    select INTO start_day $2;

    for i in 0 .. ($3 - $2) loop
    IF start_day >= $4 AND start_day <= $5 THEN
    current_day = EXTRACT(dow FROM start_day);
    IF current_day = 0 THEN
    current_day = 7;
    END IF;

    select INTO week_day array_append(week_day, current_day);
    END IF;

    start_day = start_day + 1;

    end loop;

    for i in 1 .. array_length(week_day, 1) loop
    if position(week_day[i]::character varying in $1) > 0 then
    count = count+1;
    end if;
    end loop;

    RETURN count;

    END;
    $BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100;


    get_price_profile_id_room_right(id_user integer, id_country integer, id_region integer, id_city integer, id_hotel integer, id_room integer, id_food_type integer, id_placement integer, def_id_price_profile integer)
    RETURNS integer AS
    $BODY$
    DECLARE
    value_id_price_profile integer;


    BEGIN
    SELECT INTO value_id_price_profile
    id_price_profile
    FROM nl_room_right
    WHERE
    ((nl_room_right.id_room = id_room
    AND nl_room_right.id_food_type = id_food_type
    AND nl_room_right.id_placement = id_placement)
    OR
    (nl_room_right.id_hotel = id_hotel
    AND nl_room_right.id_food_type = id_food_type)
    OR
    (nl_room_right.id_city = id_city
    AND nl_room_right.id_food_type = id_food_type)
    OR
    (nl_room_right.id_region = id_region
    AND nl_room_right.id_food_type = id_food_type)
    OR
    (nl_room_right.id_country = id_country
    AND nl_room_right.id_food_type = id_food_type)


    OR
    (nl_room_right.id_food_type = id_food_type
    AND nl_room_right.id_placement = 0
    AND nl_room_right.id_room = 0
    AND nl_room_right.id_hotel = 0
    AND nl_room_right.id_region = 0
    AND nl_room_right.id_country = 0
    )

    )
    AND (nl_room_right.id_user = 0 OR nl_room_right.id_user = id_user)

    AND nl_room_right.active = TRUE

    ORDER BY id_placement,id_room,id_hotel,id_city,id_region,id_country,id_user DESC

    LIMIT 1;

    if value_id_price_profile IS NOT NULL then
    return value_id_price_profile;
    end if;

    RETURN def_id_price_profile;

    markup(id_user integer, id_hotel integer, price numeric)
    RETURNS numeric AS
    $BODY$

    DECLARE
    markup numeric(10,2);
    divisor numeric(10,2);
    result numeric(10,2);

    BEGIN

    SELECT INTO markup m.markup_value
    FROM nl_user_markup m
    WHERE m.active = true
    AND m.id_user = $1
    AND m.id_hotel = $2
    LIMIT 1;

    IF markup IS NULL THEN
    SELECT INTO markup m.markup_value
    FROM nl_user_markup m
    WHERE m.active = true
    AND m.id_user = $1
    AND m.id_city = (SELECT h.id_city FROM nl_hotel h WHERE h.id_hotel = $2)
    LIMIT 1;

    IF markup IS NULL THEN
    SELECT INTO markup m.markup_value
    FROM nl_user_markup m
    WHERE m.active = true
    AND m.id_user = $1
    AND m.id_region = (SELECT id_region FROM nl_city WHERE id_city = (SELECT h.id_city FROM nl_hotel h WHERE h.id_hotel = $2))
    LIMIT 1;

    IF markup IS NULL THEN
    SELECT INTO markup m.markup_value
    FROM nl_user_markup m
    WHERE m.active = true
    AND m.id_user = $1
    AND m.id_country = (SELECT id_country FROM nl_region WHERE id_region = (SELECT id_region FROM nl_city WHERE id_city = (SELECT h.id_city FROM nl_hotel h WHERE h.id_hotel = $2)))
    LIMIT 1;

    IF markup IS NULL THEN
    SELECT INTO markup m.markup_value
    FROM nl_user_markup m
    WHERE m.active = true
    AND m.id_user = $1
    AND m.id_hotel = 0
    AND m.id_city = 0
    AND m.id_region = 0
    AND m.id_country = 0
    LIMIT 1;
    END IF;
    END IF;
    END IF;
    END IF;


    IF markup IS NULL THEN
    result = 0;

    ELSE
    divisor = (1 - (markup/100));

    IF divisor = 0 THEN result = $3 * 2;
    ELSE IF divisor > 0 THEN result = $3 / divisor; END IF;
    END IF;


    IF result <= 0 THEN result = $3; END IF;

    result = result - $3;
    END IF;

    RETURN result;

    nl_round(price numeric)
    RETURNS numeric AS
    $BODY$
    DECLARE result numeric;

    BEGIN
    IF $1 - floor($1) >= 0.1
    THEN result = ceil($1);
    ELSE result = floor($1);
    END IF;

    RETURN result;


    recalc_in_add_currency(price numeric, id_add_currency integer, date_begin date)
    RETURNS numeric AS
    $BODY$

    DECLARE
    result numeric(10,2);
    num_days int;
    rate numeric;
    coefficient_before numeric(10,2);
    coefficient_after numeric(10,2);

    BEGIN

    SELECT c.num_days, c.coefficient_before, c.coefficient_after, r.rate INTO num_days, coefficient_before, coefficient_after, rate
    FROM nl_add_currency c, nl_add_currency_rate r
    WHERE c.active = true
    AND c.id_add_currency = $2
    AND c.id_add_currency_rate = r.id_add_currency_rate
    AND r.active = true;

    IF rate IS NULL THEN result = $1;
    ELSE IF date_part('day', age($3, now())) < num_days THEN result = $1 * rate * coefficient_before;
    ELSE result = $1 * rate * coefficient_after;
    END IF;
    END IF;

    RETURN result;

    room_price_sum(date_begin date, date_end date, id_room integer, id_placement integer, id_food_type integer, id_currency integer, id_price_profile integer, id_add_currency integer, id_user integer)
    RETURNS numeric AS
    $BODY$
    DECLARE
    retur_sum numeric(10,2);
    count_days int;

    BEGIN
    SELECT SUM(pr.price*pr.same_days), SUM(pr.same_days) INTO retur_sum, count_days
    FROM (
    SELECT
    (CASE WHEN $8 = 0
    THEN (nl_round(nl_price_catalog.price) + nl_round(markup($9, nl_room.id_hotel, nl_price_catalog.price)))
    ELSE (nl_round(recalc_in_add_currency(nl_price_catalog.price, $8, $1)) + nl_round(recalc_in_add_currency(markup($9, nl_room.id_hotel, nl_price_catalog.price), $8, $1)))
    END) as price,
    count_same_week_day(nl_period.week_day, $1, $2, nl_period.date_begin, nl_period.date_end) as same_days

    FROM
    nl_room,
    nl_room_placement,
    nl_room_food_type,
    nl_price_catalog,
    nl_period

    WHERE
    nl_room.id_room = $3
    AND nl_room_placement.id_placement = $4
    AND nl_room_food_type.id_food_type = $5
    AND nl_price_catalog.status IN (1, 2, 5)
    AND nl_price_catalog.id_currency = $6
    AND nl_price_catalog.id_price_profile = $7
    AND nl_period.active = true
    AND (($1 >= nl_period.date_begin AND $2 >= nl_period.date_end AND $1 <= nl_period.date_end) OR
    ($1 <= nl_period.date_begin AND $2 >= nl_period.date_end) OR
    ($1 <= nl_period.date_begin AND $2 <= nl_period.date_end AND $2 >= nl_period.date_begin) OR
    ($1 >= nl_period.date_begin AND $2 <= nl_period.date_end))


    AND nl_room_placement.id_room = nl_room.id_room
    AND nl_room_food_type.id_room = nl_room.id_room
    AND nl_room.id_room = nl_price_catalog.id_room
    AND nl_price_catalog.id_placement = nl_room_placement.id_placement
    AND nl_price_catalog.id_food_type = nl_room_food_type.id_food_type
    AND nl_price_catalog.id_period = nl_period.id_period
    ) as pr;

    IF count_days < ($2-$1) THEN retur_sum = 0; END IF;

    RETURN retur_sum;

    Version - PostgreSQL 8.4.11
    Maintenance Setup - manual VACUUM once a week
    History - this query always slow. In DB nothing has not changed.

    TABLES
    nl_price_catalog
    (
    id_price_catalog serial NOT NULL,
    id_room integer NOT NULL,
    id_currency integer NOT NULL,
    id_placement integer NOT NULL,
    id_food_type integer NOT NULL,
    id_price_profile integer NOT NULL,
    id_formula integer NOT NULL,
    id_period integer NOT NULL,
    status smallint NOT NULL DEFAULT 0, -- 0 - unknown...
    price numeric(10,2) NOT NULL DEFAULT 0.00,
    CONSTRAINT prime_id_price_catalog PRIMARY KEY (id_price_catalog),
    CONSTRAINT nl_currency_nl_price_catalog_fk FOREIGN KEY (id_currency)
    REFERENCES nl_currency (id_currency) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT nl_food_type_nl_price_catalog_fk FOREIGN KEY (id_food_type)
    REFERENCES nl_food_type (id_food_type) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT nl_period_formula_nl_price_catalog_fk FOREIGN KEY (id_formula)
    REFERENCES nl_period_formula (id_formula) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT nl_period_nl_price_catalog_fk FOREIGN KEY (id_period)
    REFERENCES nl_period (id_period) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT nl_placement_nl_price_catalog_fk FOREIGN KEY (id_placement)
    REFERENCES nl_placement (id_placement) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT nl_price_profile_nl_price_catalog_fk FOREIGN KEY (id_price_profile)
    REFERENCES nl_price_profile (id_price_profile) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT nl_room_nl_price_catalog_fk FOREIGN KEY (id_room)
    REFERENCES nl_room (id_room) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT
    )
    WITH (
    OIDS=FALSE
    );

    nl_price_catalog_idx
    ON nl_price_catalog
    USING btree
    (id_price_profile, id_currency, id_room, id_placement, id_food_type);

    nl_period
    (
    id_period serial NOT NULL,
    date_begin date NOT NULL,
    date_end date NOT NULL,
    week_day character varying(32),
    active boolean NOT NULL DEFAULT false,
    id_room integer NOT NULL,
    tab_period_description smallint NOT NULL DEFAULT 0,
    tab_period_formula_netto smallint NOT NULL DEFAULT 0,
    tab_period_formula_catalog smallint NOT NULL DEFAULT 0,
    tab_period_netto_price smallint NOT NULL DEFAULT 0,
    tab_period_catalog_price smallint NOT NULL DEFAULT 0,
    id_maincurrency integer NOT NULL DEFAULT 0,
    tab_period_rr_price smallint NOT NULL DEFAULT 0,
    tab_period_formula_rr smallint NOT NULL DEFAULT 0,
    CONSTRAINT prime_id_period PRIMARY KEY (id_period),
    CONSTRAINT nl_currency_nl_period_fk FOREIGN KEY (id_maincurrency)
    REFERENCES nl_currency (id_currency) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT nl_room_nl_period_fk FOREIGN KEY (id_room)
    REFERENCES nl_room (id_room) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT
    )
    WITH (
    OIDS=FALSE
    );

    nl_period_active_idx
    ON nl_period
    USING btree
    (id_period, id_room, date_begin, date_end)
    WHERE active = true;

    nl_placement
    (
    id_placement serial NOT NULL,
    "name" character varying(8) NOT NULL,
    active boolean NOT NULL DEFAULT true,
    num_adult smallint NOT NULL DEFAULT 0,
    num_children smallint NOT NULL DEFAULT 0,
    CONSTRAINT prime_id_placement PRIMARY KEY (id_placement)
    )
    WITH (
    OIDS=FALSE
    );

    nl_room
    (
    id_room serial NOT NULL,
    id_room_type integer NOT NULL DEFAULT 0,
    id_hotel integer NOT NULL,
    id_maincurrency integer NOT NULL,
    active boolean NOT NULL DEFAULT true,
    tab_description smallint NOT NULL DEFAULT 0,
    tab_parametr smallint NOT NULL DEFAULT 0,
    tab_formula_netto smallint NOT NULL DEFAULT 0,
    tab_formula_catalog smallint NOT NULL DEFAULT 0,
    tab_rr smallint NOT NULL DEFAULT 0,
    tab_period smallint NOT NULL DEFAULT 0,
    tab_formula_rr smallint NOT NULL DEFAULT 0,
    view_order integer,
    use_action boolean NOT NULL DEFAULT false, -- TRUE - use action tariffs;...
    tab_action smallint NOT NULL DEFAULT 0,
    CONSTRAINT prime_id_room PRIMARY KEY (id_room),
    CONSTRAINT nl_currency_nl_room_fk FOREIGN KEY (id_maincurrency)
    REFERENCES nl_currency (id_currency) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT nl_hotel_nl_room_fk FOREIGN KEY (id_hotel)
    REFERENCES nl_hotel (id_hotel) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT
    )
    WITH (
    OIDS=FALSE
    );

    nl_city
    (
    id_city serial NOT NULL,
    id_region integer NOT NULL,
    cod character varying(8),
    active boolean NOT NULL DEFAULT false,
    CONSTRAINT prime_id_city PRIMARY KEY (id_city),
    CONSTRAINT nl_region_nl_city_fk FOREIGN KEY (id_region)
    REFERENCES nl_region (id_region) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT
    )
    WITH (
    OIDS=FALSE
    );

    nl_region
    (
    id_region serial NOT NULL,
    id_country integer NOT NULL,
    active boolean NOT NULL DEFAULT false,
    CONSTRAINT prime_id_region PRIMARY KEY (id_region),
    CONSTRAINT nl_country_nl_region_fk FOREIGN KEY (id_country)
    REFERENCES nl_country (id_country) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT
    )
    WITH (
    OIDS=FALSE
    );

    nl_country
    (
    id_country serial NOT NULL,
    active boolean NOT NULL DEFAULT false,
    iso_code_2 character(2),
    iso_code_3 character(3),
    CONSTRAINT prime_id_country PRIMARY KEY (id_country)
    )
    WITH (
    OIDS=FALSE
    );


    nl_currency
    (
    id_currency serial NOT NULL,
    "name" character varying(8) NOT NULL,
    ico character varying(16) NOT NULL,
    active boolean NOT NULL DEFAULT false,
    view_order integer NOT NULL,
    CONSTRAINT prime_id_currency PRIMARY KEY (id_currency)
    )
    WITH (
    OIDS=FALSE
    );

    nl_hotel
    (
    id_hotel serial NOT NULL,
    id_hotel_class integer,
    id_maincurrency integer,
    id_city integer NOT NULL,
    active boolean NOT NULL DEFAULT false,
    tab_description smallint NOT NULL DEFAULT 0,
    tab_formula_netto smallint NOT NULL DEFAULT 0,
    tab_formula_catalog smallint NOT NULL DEFAULT 0,
    tab_allotment smallint NOT NULL DEFAULT 0,
    tab_formula_rr smallint NOT NULL DEFAULT 0,
    postcode character varying(64),
    phone_reception character varying(16),
    phone_booking character varying(16),
    fax character varying(16),
    qr_code character varying(16), -- File name
    url character varying(512),
    latitude double precision,
    longitude double precision,
    child_age_free smallint,
    child_age smallint,
    time_arrival time without time zone,
    time_departure time without time zone,
    tab_formula_overtime smallint NOT NULL DEFAULT 0,
    time_arrival_early time without time zone,
    arrival_early boolean DEFAULT false,
    time_departure_late time without time zone,
    departure_late boolean DEFAULT false,
    penalty_begin time without time zone,
    price_show boolean DEFAULT false,
    penalty_time smallint,
    rule_book_source boolean DEFAULT false, -- TRUE - take from hotel...
    rule_cancel_source boolean DEFAULT false, -- TRUE - take from hotel...
    view_order_hotel integer,
    id_user integer, -- user for which hotel is attached
    popularity integer NOT NULL DEFAULT 0,
    id_provider_mailtype integer NOT NULL DEFAULT 0,
    use_float_rr boolean NOT NULL DEFAULT false, -- FALSE - not use float rr...
    tab_float_price_rr smallint NOT NULL DEFAULT 0,
    tab_param smallint NOT NULL DEFAULT 0,
    CONSTRAINT nl_hotel_pkey PRIMARY KEY (id_hotel),
    CONSTRAINT nl_city_nl_hotel_fk FOREIGN KEY (id_city)
    REFERENCES nl_city (id_city) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT nl_currency_nl_hotel_fk FOREIGN KEY (id_maincurrency)
    REFERENCES nl_currency (id_currency) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT nl_hotel_class_nl_hotel_fk FOREIGN KEY (id_hotel_class)
    REFERENCES nl_hotel_class (id_hotel_class) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT,
    CONSTRAINT nl_users_nl_hotel_fk FOREIGN KEY (id_user)
    REFERENCES nl_users (id_user) MATCH SIMPLE
    ON UPDATE CASCADE ON DELETE RESTRICT
    )
    WITH (
    OIDS=FALSE
    );


    Server Configuration
    checkpoint_segments 64;
    client_encoding UNICODE;
    DateStyle ISO, DMY;
    default_text_search_config pg_catalog.russian;
    effective_cache_size 2GB;
    external_pid_file /var/run/postgresql/8.4-main.pid;
    lc_messages ru_UA.utf8;
    lc_monetary ru_UA.utf8;
    lc_numeric ru_UA.utf8;
    lc_time ru_UA.utf8;
    listen_addresses *;
    log_line_prefix %t;
    log_timezone localtime;
    maintenance_work_mem 256MB;
    max_connections 100;
    max_stack_depth 2MB;
    password_encryption on;
    port 5432;
    shared_buffers 512MB;
    ssl on;
    TimeZone Europe/Kiev;
    timezone_abbreviations Default;
    unix_socket_directory /var/run/postgresql;
    wal_buffers 8MB;
    work_mem 48MB;
  12. #7
  13. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    Wow, ok, perhaps you should start by looking at the queries :-)

    AND (('2013-12-23'>= nl_period.date_begin AND '2013-12-29' >= nl_period.date_end AND '2013-12-23' <= nl_period.date_end) OR
    ('2013-12-23'<= nl_period.date_begin AND '2013-12-29' >= nl_period.date_end) OR
    ('2013-12-23'<= nl_period.date_begin AND '2013-12-29' <= nl_period.date_end AND '2013-12-29' >= nl_period.date_begin) OR
    ('2013-12-23'>= nl_period.date_begin AND '2013-12-29' <= nl_period.date_end))
    Take a look at OVERLAPS(): http://www.postgresql.org/docs/9.3/static/functions-datetime.html

    So you can just do:

    AND ('2013-12-23','2013-12-29) OVERLAPS (nl_period.date_begin, nl_period.date_end)

    There are quite a few other cases where you repeat conditions over and over, like:

    WHERE
    ((nl_room_right.id_room = id_room
    AND nl_room_right.id_food_type = id_food_type
    AND nl_room_right.id_placement = id_placement)
    OR
    (nl_room_right.id_hotel = id_hotel
    AND nl_room_right.id_food_type = id_food_type)
    OR
    (nl_room_right.id_city = id_city
    AND nl_room_right.id_food_type = id_food_type)
    OR
    (nl_room_right.id_region = id_region
    AND nl_room_right.id_food_type = id_food_type)
    OR
    (nl_room_right.id_country = id_country
    AND nl_room_right.id_food_type = id_food_type)
    Which is the same as:

    WHERE
    ((nl_room_right.id_room = id_room
    AND nl_room_right.id_food_type = id_food_type
    AND nl_room_right.id_placement = id_placement)
    OR
    (nl_room_right.id_food_type = id_food_type
    and (
    nl_room_right.id_hotel = id_hotel
    OR
    nl_room_right.id_city = id_city
    OR
    nl_room_right.id_region = id_region
    OR
    nl_room_right.id_country = id_country)


    You should probably also use the explicite JOIN syntax, rather than supplying conditions in the WHERE clause.

    Also, which query does the explain belong to? There are a few seqscans but only the one on nl_price_catalog seems to hit many records.
  14. #8
  15. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    5
    Rep Power
    0
    Also, which query does the explain belong to?
    Explain of "My Query".

    P.S. Thanks for recommendations to improve the query.
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    Ah yes, I should have known, with the explain being right under the query and all... :-)

    You say you vacuum once a week, why don't you use the autovacuum feature?


    A few more things about the I would change:
    GROUP BY. You are grouping by a ton of columns, which indicates to me that one of you JOIN statements is creating semi-duplicate records that you are trying to filter out.
    It may help performance if you first do a (sub)query that fetches only the PK of the hotels that meet your requirements, and then use a second (outer)query that fetches the details that you need for each hotel. That would remove the need for the pointless grouping.

    Of course, the use of functions in your WHERE clause won't do much good for performance because their value cannot be predicted so indexes can't be used (apart from the fact that they have to be executed in the first place to get the value out). It may be worth investigating how many records you are filtering with this so you can move the condition to an outer query, forcing the database to apply the other conditions first.
    It may even be worth using a materialized view for that data so you can just do a JOIN and make full use of indexes.

    I'd do these things first, and then see what EXPLAIN has to say about it.
  18. #10
  19. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2013
    Posts
    5
    Rep Power
    0
    Will increase execution speed when Raid10 HDD change on Raid10 SSD?
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2013
    Posts
    158
    Rep Power
    10
    Adding faster disks is not going to reduce the workload of the query. Most of the relevant data will already be in memory which is faster than any SSD.

    But you can test to see if your server has lots of HD activity from PostgreSQL when you run the query. If it does though, I'd sooner add more RAM then an SSD because all the work is done in RAM, not on disk.

    But the point is: your query is horribly inefficient, fix it.

IMN logo majestic logo threadwatch logo seochat tools logo