[GENERAL] Planner statistics usage for composite type

Поиск
Список
Период
Сортировка
От Dmitry Lazurkin
Тема [GENERAL] Planner statistics usage for composite type
Дата
Msg-id 7adc3173-c959-0b6d-3333-dd7505c00ae2@gmail.com
обсуждение исходный текст
Ответы Re: [GENERAL] Planner statistics usage for composite type
Список pgsql-general
Hello.

I am trying to find workaround for cross-column statistics. For example,
I have tags with similarity:
select version();
                                                      version
-------------------------------------------------------------------------------------------------------------------
 PostgreSQL 10beta2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu
5.4.0-6ubuntu1~16.04.4) 5.4.0 20160609, 64-bit

CREATE TABLE tags(
       t varchar(30) NOT NULL,
       s double precision
);
INSERT INTO tags (SELECT 'tag1'::text as t, 0.7 as s from
generate_series(0, 10000));

I think i can create index for cross-column statistics:
CREATE TYPE tag_sim AS (
       t varchar(30),
       s double precision
);
CREATE INDEX tags_composite ON tags USING btree ((ROW(t, s)::tag_sim));
ANALYZE tags;
SELECT * FROM pg_stats WHERE tablename = 'tags_composite';
-[ RECORD 1 ]----------+---------------
schemaname             | public
tablename              | tags_composite
attname                | row
inherited              | f
null_frac              | 0
avg_width              | 40
n_distinct             | 1
most_common_vals       | {"(tag1,0.7)"}
most_common_freqs      | {1}
histogram_bounds       | (null)
correlation            | 1
most_common_elems      | (null)
most_common_elem_freqs | (null)
elem_count_histogram   | (null)

OK, I have statistics. Search:
EXPLAIN SELECT * FROM tags WHERE ROW(t, s)::tag_sim >= ROW('tag1',
0.9)::tag_sim AND
ROW(t, s)::tag_sim <= ROW('tag1', 1.0)::tag_sim;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Index Scan using tags_composite on tags  (cost=0.29..9.29 rows=50 width=13)
   Index Cond: ((ROW(t, s)::tag_sim >= ROW('tag1', 0.9)) AND (ROW(t,
s)::tag_sim <= ROW('tag1', 1.0)))
(2 rows)

Worn estimate. Planner doesn't use statistics. In code I see usage of
function scalargtsel which returns default selectivity because
ROW('tag1', 0.9)::tag_sim is not Const.

May be someone known how to fix this issue?

Thanks.



В списке pgsql-general по дате отправления:

Предыдущее
От: vinny
Дата:
Сообщение: Re: [GENERAL] Is it possible to define a constraint based on thevalues in other rows in the current table?
Следующее
От: Tom Lane
Дата:
Сообщение: Re: [GENERAL] Planner statistics usage for composite type