From: | "dilaz03 (dot)" <dilaz03(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Planner statistics usage for composite type |
Date: | 2017-07-15 13:56:32 |
Message-ID: | CALUfWraGgp9ARieuBd6fYS+Q9fo-5M6b-G+-Cg_Y7KeuVrCuiQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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.
From | Date | Subject | |
---|---|---|---|
Next Message | postgresql | 2017-07-15 18:56:27 | spi/timetravel: unique constraint violation on UPDATE |
Previous Message | Matthew Byrne | 2017-07-15 13:49:16 | Support for \u0000? |