pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedIndex not being used on composite type for particular query
3+ messages / 2 participants
[nested] [flat]
* Index not being used on composite type for particular query
@ 2017-05-20 23:33 Zac Goldstein <goldzz@gmail.com>
2017-05-21 00:00 ` Re: Index not being used on composite type for particular query Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 3+ messages in thread
From: Zac Goldstein @ 2017-05-20 23:33 UTC (permalink / raw)
To: pgsql-performance
Hello,
The index on my composite type seems to be working most of the time, but
there's a query I need to run where it's not working even with
enable_seqscan=false. The composite type uses int and numrange subcolumns,
and is designed to operate primarily as a range type.
It will probably be easier for you to read the rest of this from my
stackexchange post but I'll copy and paste the contents of it here as well.
https://dba.stackexchange.com/questions/174099/postgres-composite-type-not-using-index
The queries in this example are for testing purposes. It's possible for me
to get the index to work by using the int and numrange separately rather
than creating a new matchsecond_type, but using the composite type makes
things much easier further down the pipeline where I have to tie this in
with an ORM.
This should include everything necessary to test it out yourself.
-----------------------------------------------
I'm using: `PostgreSQL 9.6.2 on x86_64-pc-linux-gnu, compiled by gcc
(Ubuntu 6.3.0-5ubuntu1) 6.3.0 20170124, 64-bit`
And for the purposes of this testing `SET enable_seqscan=false`.
This uses the index:
EXPLAIN ANALYZE SELECT * FROM shot
WHERE lower(shot.matchsecond) <@ (0, numrange(5, 10))::matchsecond_type;
Bitmap Heap Scan on shot (cost=471.17..790.19 rows=50 width=45)
(actual time=2.601..29.555 rows=5 loops=1)
Recheck Cond: (((matchsecond).match_id)::integer = (0)::integer)
Filter: ((numrange(lower(((matchsecond).second)::numrange),
lower(((matchsecond).second)::numrange), '[]'::text))::numrange <@
('[5,10)'::numrange)::numrange)
Rows Removed by Filter: 9996
Heap Blocks: exact=94
Buffers: shared hit=193
-> Bitmap Index Scan on ix_shot_matchsecond (cost=0.00..471.16
rows=10001 width=0) (actual time=2.516..2.516 rows=10001 loops=1)
Index Cond: (((matchsecond).match_id)::integer = (0)::integer)
Buffers: shared hit=99
Planning time: 0.401 ms
Execution time: 29.623 ms
But this doesn't:
EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM shot
WHERE lower(shot.matchsecond) <@ ((shot.matchsecond).match_id,
numrange(5, 10))::matchsecond_type;
Seq Scan on shot (cost=10000000000.00..10000000319.02 rows=1 width=45)
(actual time=0.091..20.003 rows=5 loops=1)
Filter: ((((matchsecond).match_id)::integer =
((matchsecond).match_id)::integer) AND
((numrange(lower(((matchsecond).second)::numrange),
lower(((matchsecond).second)::numrange), '[]'::text))::numrange <@
('[5,10)'::numrange)::numrange))
Rows Removed by Filter: 9996
Buffers: shared hit=94
Planning time: 0.351 ms
Execution time: 20.075 ms
Note the `0` in the first compared to `(shot.matchsecond).match_id` in the
second on the right hand side of the `<@`. Interestingly, if the left hand
side is simply `shot.matchsecond` instead of `lower(shot.matchsecond)`, the
query manages to use the index. The index is also used when constructing
the numrange with functions like
`numrange(lower((shot.matchsecond).second), lower((shot.matchsecond).second
+ 10))`.
Here are the relevant definitions:
CREATE DOMAIN matchsecond_match AS integer NOT NULL;
CREATE DOMAIN matchsecond_second AS numrange NOT NULL CHECK(VALUE <>
numrange(0,0));
CREATE TYPE matchsecond_type AS (
match_id matchsecond_match,
second matchsecond_second
);
CREATE OR REPLACE FUNCTION matchsecond_contains_range(matchsecond_type,
matchsecond_type)
RETURNS BOOLEAN AS $$ SELECT $1.match_id = $2.match_id AND $1.second @>
$2.second $$
LANGUAGE SQL;
CREATE OPERATOR @> (
LEFTARG = matchsecond_type,
RIGHTARG = matchsecond_type,
PROCEDURE = matchsecond_contains_range,
COMMUTATOR = <@,
RESTRICT = eqsel,
JOIN = eqjoinsel
);
CREATE OR REPLACE FUNCTION
matchsecond_contained_by_range(matchsecond_type, matchsecond_type)
RETURNS BOOLEAN AS $$ SELECT $1.match_id = $2.match_id AND $1.second <@
$2.second $$
LANGUAGE SQL;
CREATE OPERATOR <@ (
LEFTARG = matchsecond_type,
RIGHTARG = matchsecond_type,
PROCEDURE = matchsecond_contained_by_range,
COMMUTATOR = @>,
RESTRICT = eqsel,
JOIN = eqjoinsel
);
CREATE OR REPLACE FUNCTION lower(matchsecond_type)
RETURNS matchsecond_type AS
$$ SELECT ($1.match_id, numrange(lower($1.second), lower($1.second),
'[]'))::matchsecond_type $$
LANGUAGE SQL;
And a test table:
Reminder: Use `CREATE EXTENSION btree_gist;`
CREATE TABLE shot AS(
SELECT i AS id, (0, numrange(i, i+1))::matchsecond_type AS
matchsecond
FROM generate_series(0,10000) AS i
);
ALTER TABLE shot ADD PRIMARY KEY (id);
CREATE INDEX ix_shot_matchsecond
ON shot
USING gist (((matchsecond).match_id), ((matchsecond).second));
----------------------------------------------
Thank you
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Index not being used on composite type for particular query
2017-05-20 23:33 Index not being used on composite type for particular query Zac Goldstein <goldzz@gmail.com>
@ 2017-05-21 00:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-05-21 02:51 ` Re: Index not being used on composite type for particular query Zac Goldstein <goldzz@gmail.com>
0 siblings, 1 reply; 3+ messages in thread
From: Tom Lane @ 2017-05-21 00:00 UTC (permalink / raw)
To: Zac Goldstein <goldzz@gmail.com>; +Cc: pgsql-performance
Zac Goldstein <goldzz@gmail.com> writes:
> This uses the index:
> ...
> But this doesn't:
> EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM shot
> WHERE lower(shot.matchsecond) <@ ((shot.matchsecond).match_id,
> numrange(5, 10))::matchsecond_type;
Well, yeah. After inlining the SQL functions, what you have is
> Filter: ((((matchsecond).match_id)::integer =
> ((matchsecond).match_id)::integer) AND
> ((numrange(lower(((matchsecond).second)::numrange),
> lower(((matchsecond).second)::numrange), '[]'::text))::numrange <@
> ('[5,10)'::numrange)::numrange))
and neither half of the AND has the form "indexed_value indexable_operator
constant", which is the basic requirement for an index condition. We're a
little bit permissive about what "constant" means, but that most certainly
doesn't extend to expressions involving columns of the table. So the
first clause loses because it's got variables on both sides, and the
second loses because the LHS expression is not what the index is on.
You could build an additional index on that expression, if this shape
of query is important enough to you to justify maintaining another index.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
^ permalink raw reply [nested|flat] 3+ messages in thread
* Re: Index not being used on composite type for particular query
2017-05-20 23:33 Index not being used on composite type for particular query Zac Goldstein <goldzz@gmail.com>
2017-05-21 00:00 ` Re: Index not being used on composite type for particular query Tom Lane <tgl@sss.pgh.pa.us>
@ 2017-05-21 02:51 ` Zac Goldstein <goldzz@gmail.com>
0 siblings, 0 replies; 3+ messages in thread
From: Zac Goldstein @ 2017-05-21 02:51 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
Thanks for the fast reply and explanation, Tom. Overall, I have been
pleasantly surprised with the leniency of indexes on range types.
On Sat, May 20, 2017 at 5:00 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Zac Goldstein <goldzz@gmail.com> writes:
> > This uses the index:
> > ...
> > But this doesn't:
>
> > EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM shot
> > WHERE lower(shot.matchsecond) <@ ((shot.matchsecond).match_id,
> > numrange(5, 10))::matchsecond_type;
>
> Well, yeah. After inlining the SQL functions, what you have is
>
> > Filter: ((((matchsecond).match_id)::integer =
> > ((matchsecond).match_id)::integer) AND
> > ((numrange(lower(((matchsecond).second)::numrange),
> > lower(((matchsecond).second)::numrange), '[]'::text))::numrange <@
> > ('[5,10)'::numrange)::numrange))
>
> and neither half of the AND has the form "indexed_value indexable_operator
> constant", which is the basic requirement for an index condition. We're a
> little bit permissive about what "constant" means, but that most certainly
> doesn't extend to expressions involving columns of the table. So the
> first clause loses because it's got variables on both sides, and the
> second loses because the LHS expression is not what the index is on.
>
> You could build an additional index on that expression, if this shape
> of query is important enough to you to justify maintaining another index.
>
> regards, tom lane
>
^ permalink raw reply [nested|flat] 3+ messages in thread
end of thread, other threads:[~2017-05-21 02:51 UTC | newest]
Thread overview: 3+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2017-05-20 23:33 Index not being used on composite type for particular query Zac Goldstein <goldzz@gmail.com>
2017-05-21 00:00 ` Tom Lane <tgl@sss.pgh.pa.us>
2017-05-21 02:51 ` Zac Goldstein <goldzz@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox