[d.tereschuk]

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?

[gw1500se]

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.

[ManiacDan]

welcome to the postgres forum.

[Vinny42]

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?

[shammat]

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

[d.tereschuk]

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;

[Vinny42]

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.

[d.tereschuk]

Also, which query does the explain belong to?

Explain of "My Query".

P.S. Thanks for recommendations to improve the query.

[Vinny42]

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.

[d.tereschuk]

Will increase execution speed when Raid10 HDD change on Raid10 SSD?

[Vinny42]

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.