Planner statistics usage for composite type

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.

Browse pgsql-general by date

  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?