with vertex_cost as (
select fid_id, sum(vertex_cost) as vertex_cost /*min, max аналогично*/ from vertex group by fid_id
),
edge_cost as (
select fid_id, sum(edge_cost) as edge_cost /*min, max*/ from edge group by fid_id
)
select id, name, vertex_cost, edge_cost
from fig
left join vertex_cost vc on fig.id=vc.fig_id
left join edge_cost ec on fig.id=ec.fig_idwith figures as (
select id, name from figures where name like 'foo%'
),
vertex_cost as (
select fid_id, sum(vertex_cost) as vertex_cost /*min, max*/ from vertex where fig_id in (select id from figures) group by fid_id
),
edge_cost as (
select fid_id, sum(edge_cost) as edge_cost /*min, max*/ from edge where fig_id in (select id from figures) group by fid_id
)
select id, name, vertex_cost, edge_cost
from figures
left join vertex_cost vc on figures.id=vc.fig_id
left join edge_cost ec on figures.id=ec.fig_idAll constraints on all partitions of the master table are examined during constraint exclusion, so large numbers of partitions are likely to increase query planning time considerably. Partitioning using these techniques will work well with up to perhaps a hundred partitions; don't try to use many thousands of partitions.
return query select ....CREATE OR REPLACE FUNCTION public.get_full_info_about_passenger(INTEGER)
RETURNS TABLE(
id bigint
)
AS
$BODY$
BEGIN
RETURN QUERY SELECT p.id FROM public.passengers p;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;CREATE OR REPLACE FUNCTION public.get_full_info_about_passenger(INTEGER)
RETURNS setof bigint
AS
$BODY$
BEGIN
RETURN QUERY SELECT id FROM public.passengers;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;CREATE OR REPLACE FUNCTION public.get_full_info_about_passenger(INTEGER)
RETURNS setof public.passengers
AS
$BODY$
BEGIN
RETURN QUERY SELECT * FROM public.passengers;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;pg_read_file Return the contents of a text file.
select
user_id,
date,
SUM(amount) filter(where currency = 'USD') AS USD,
SUM(amount) filter(where currency = 'USD') AS EUR
FROM Table1
GROUP BY user_id, datewith table2_insert as (
INSERT INTO table2 (column1) VALUES ('test') RETURNING id
)
INSERT INTO table1 (column1) select id from table2_insertCREATE UNIQUE INDEX test_good_default_goods ON test_good (good_id)
WHERE by_default = trueupdate test_good set by_default = true where id = :idupdate test_good set by_default = (id = :new_default_id) where good_id = :good_idexplain analyze select max(time) from params where counter_id in (6001, 15998);Aggregate (cost=18.52..18.53 rows=1 width=8) (actual time=1.586..1.586 rows=1 loops=1)
-> Index Only Scan using params_counter_id_time_idx on params (cost=0.56..18.51 rows=4 width=8) (actual time=0.769..1.566 rows=12 loops=1)
Index Cond: (counter_id = ANY ('{6001,15998}'::bigint[]))
Heap Fetches: 2
Planning time: 50.191 ms
Execution time: 1.933 ms
Aggregate (cost=25.20..25.21 rows=1 width=8) (actual time=38.797..38.797 rows=1 loops=1)
-> Bitmap Heap Scan on params (cost=9.16..25.19 rows=4 width=8) (actual time=0.362..38.749 rows=12 loops=1)
Recheck Cond: ((counter_id = 6001) OR (counter_id = 15998))
Heap Blocks: exact=12
-> BitmapOr (cost=9.16..9.16 rows=4 width=0) (actual time=0.042..0.042 rows=0 loops=1)
-> Bitmap Index Scan on params_counter_id_time_idx (cost=0.00..4.58 rows=2 width=0) (actual time=0.031..0.031 rows=6 loops=1)
Index Cond: (counter_id = 6001)
-> Bitmap Index Scan on params_counter_id_time_idx (cost=0.00..4.58 rows=2 width=0) (actual time=0.009..0.009 rows=6 loops=1)
Index Cond: (counter_id = 15998)
Planning time: 0.247 ms
Execution time: 39.408 ms