есть большая таблица 10 млн записей.
Думаю, как оптимизировать выборку.
Мне всегда надо будет только часть результата(limit ... offset ...), все записи не нужны.

Я могу разделить эту таблицу на три разные физически, с одной структурой, и объединить их одним вью

create view v1 as
select * from t1
union
select * from t2
union
select * from t3;

Вопрос: получу ли я более быстрые запросы? , т.е. чтобы хотя бы в части случаев выборка шла не из всех физических таблиц

Oleg BartunovТебе нужно использовать partitioning + CE (constraint exclusion) и будет тебе счастье

это хорошо, но насколько я понял при partitioning + CE чтение из одной таблицы срабатывает, если в критерии where указано поле, по которому собственно partitioning.

Например я разделил таблицу по году в поле created:
t(...,s_vector tsvector, created timestamp)

разделенные
t2007(...,s_vector tsvector, created timestamp)
t2006(...,s_vector tsvector, created timestamp)
t2005(...,s_vector tsvector, created timestamp)

я так понимаю, что этому запросу не поможет деление:
select * from t where s_vector @@ to_tsquery('default','барбадос');
а вот этому поможет:
select * from t where created between 2005 and 2006 and s_vector @@ to_tsquery('default','барбадос');

или я не прав?

Andrey Daeron Winnipuhесть большая таблица 10 млн записей.
Думаю, как оптимизировать выборку.
Мне всегда надо будет только часть результата(limit ... offset ...), все записи не нужны.

Я могу разделить эту таблицу на три разные физически, с одной структурой, и объединить их одним вью

create view v1 as
select * from t1
union
select * from t2
union
select * from t3;

Вопрос: получу ли я более быстрые запросы? , т.е. чтобы хотя бы в части случаев выборка шла не из всех физических таблиц
Смотреть и копать в сторону поиска информации на форуме по ключевому слову партиционирование и наследование.
В кратце - разбивать имеет смысл только если есть критерий этого разделения, который может быть проверен. Например - дата, период .

точнее - они же должны присутствовать в запросе?

сделал главную таблицу, 4 выведенных (в зависимости), правила, триггеры... все вроде как надо.
вставляю записи в главную, они распределяются по выведенным - все ок.

проблема в том, что записи попадают и в главную, и получается в главной 100000 записей и в выведенных в сумме тоже.

как сделать так, что бы в главную не попадали?

Andrey Daeron Winnipuhсделал главную таблицу, 4 выведенных (в зависимости), правила, триггеры... все вроде как надо.
вставляю записи в главную, они распределяются по выведенным - все ок.

проблема в том, что записи попадают и в главную, и получается в главной 100000 записей и в выведенных в сумме тоже.

как сделать так, что бы в главную не попадали?
Если я правильно Вас понял то их там и нет. Т.е. при селекте из родителя и должны выбираться все записи деток.

аааааааа! видимо так и есть :-)


вот еще вопрос, правильно ли я разделил ?:
(кстати для даты индекс правильно построен или USING btree лишнее?)

show constraint_exclusion;

CREATE TABLE "public"."xprop2" (
"id_seq" SERIAL,
"node_id" "public"."uuid" NOT NULL,
"xpath" VARCHAR,
"xstring" VARCHAR,
"parent_id" "public"."uuid",
"xaccess" VARCHAR,
"xstring_vector" "public"."tsvector",
"xpath_vector" "public"."tsvector",
"node_type" INTEGER,
"node_subtype" INTEGER,
"xaccess_vector" "public"."tsvector",
"creation_date" TIMESTAMP(0) WITHOUT TIME ZONE,
"modification_date" TIMESTAMP(0) WITHOUT TIME ZONE
) WITHOUT OIDS;

CREATE INDEX xprop2_created_idx ON xprop2 USING btree (creation_date);
CREATE INDEX "xaccess2_idx" ON "public"."xprop2" USING gist ("xaccess_vector");
CREATE INDEX "xpath2_idx" ON "public"."xprop2" USING gist ("xpath_vector");
CREATE INDEX "xstring2_idx" ON "public"."xprop2" USING gist ("xstring_vector");

-- таких еще три таблицы для 5, 6, 7 года.

CREATE TABLE xprop2_04
(
CONSTRAINT "$1" CHECK
( creation_date >= '2004-01-01'::date::timestamp without time zone AND creation_date < ('2004-01-01'::date + '1 year'::interval)))
INHERITS (xprop2) WITHOUT OIDS;
CREATE INDEX xprop2_04_created_idx ON xprop2_04 USING btree (creation_date);
CREATE INDEX "xaccess2_04_idx" ON "public"."xprop2_04" USING gist ("xaccess_vector");
CREATE INDEX "xpath2_04_idx" ON "public"."xprop2_04" USING gist ("xpath_vector");
CREATE INDEX "xstring2_04_idx" ON "public"."xprop2_04" USING gist ("xstring_vector");


вот запрос для 7 года (только временной интервал) и план

m135=# explain analyze
m135-# select * from xprop2 where
m135-# creation_date > ('2007-01-01'::date + '1 month'::interval);
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..162.90 rows=2367 width=354) (actual time=0.014..4.030 rows=2319 loops=1)
-> Append (cost=0.00..162.90 rows=2367 width=354) (actual time=0.013..2.768 rows=2319 loops=1)
-> Seq Scan on xprop2 (cost=0.00..13.50 rows=93 width=252) (actual time=0.001..0.001 rows=0 loops=1)
Filter: (creation_date > '2007-02-01 00:00:00'::timestamp without time zone)
-> Seq Scan on xprop2_07 xprop2 (cost=0.00..149.40 rows=2274 width=354) (actual time=0.012..1.795 rows=2319 loops=1)
Filter: (creation_date > '2007-02-01 00:00:00'::timestamp without time zone)
Total runtime: 4.586 ms

---------------------------------------------------

а вот даты+ полнотекстовый запрос

m135=# explain analyze
m135-# select * from xprop2 where
m135-# creation_date > ('2007-01-01'::date + '1 month'::interval)
m135-# and
m35-# xstring_vector @@ to_tsquery('default','voice');
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
Result (cost=0.00..19.79 rows=3 width=354) (actual time=0.476..2.249 rows=984 loops=1)
-> Append (cost=0.00..19.79 rows=3 width=354) (actual time=0.476..1.714 rows=984 loops=1)
-> Index Scan using xstring2_idx on xprop2 (cost=0.00..8.27 rows=1 width=252) (actual time=0.005..0.005 rows=0 loops=1)
Index Cond: (xstring_vector @@ '''voic'''::tsquery)
Filter: ((creation_date > '2007-02-01 00:00:00'::timestamp without time zone) AND (xstring_vector @@ '''voic'''::tsquery))
-> Bitmap Heap Scan on xprop2_07 xprop2 (cost=4.27..11.52 rows=2 width=354) (actual time=0.469..1.296 rows=984 loops=1)
Filter: ((creation_date > '2007-02-01 00:00:00'::timestamp without time zone) AND (xstring_vector @@ '''voic'''::tsquery))
-> Bitmap Index Scan on xstring2_07_idx (cost=0.00..4.27 rows=2 width=0) (actual time=0.442..0.442 rows=984 loops=1)
Index Cond: (xstring_vector @@ '''voic'''::tsquery)
Total runtime: 2.692 ms