pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGiST, Not Using Index
5+ messages / 2 participants
[nested] [flat]
* GiST, Not Using Index
@ 2003-02-27 19:37 Itai Zukerman <zukerman@math-hat.com>
2003-02-27 20:51 ` Re: GiST, Not Using Index Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Itai Zukerman @ 2003-02-27 19:37 UTC (permalink / raw)
To: pgsql-sql
I've defined this:
[...some definitions...]
CREATE OPERATOR CLASS sig_ops
FOR TYPE sig USING gist AS
OPERATOR 7 ~>=,
FUNCTION 1 g_sig_consistent (internal, internal, int2),
FUNCTION 2 g_sig_union (bytea, internal),
FUNCTION 3 g_sig_compress (internal),
FUNCTION 4 g_sig_decompress (internal),
FUNCTION 5 g_sig_penalty (internal, internal),
FUNCTION 6 g_sig_picksplit (bytea, internal),
FUNCTION 7 g_sig_same (internal, internal, internal);
CREATE TABLE x (z sig);
CREATE INDEX xx ON x USING gist (z sig_ops);
[...insert 20000 rows into x...]
VACUUM ANALYZE x;
But I can't seem to get PG to use the index. Shouldn't this "explain
verbose" at least attempt to check the cost of using the index? Am I
not interpreting the output correctly? What am I doing wrong?
# explain verbose
# select z from x where z ~>= sig_in('{1,2,3}'::int4[]);;
QUERY PLAN
--------------------------------------------------------------------------------
{ SEQSCAN
:startup_cost 0.00
:total_cost 2826.01
:rows 24334
:width 8
:qptargetlist (
{ TARGETENTRY
:resdom
{ RESDOM
:resno 1
:restype 1802511
:restypmod -1
:resname z
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 2
:vartype 1802511
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
}
)
:qpqual (
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 1836035
:opid 1802521
:opresulttype 16
:opretset false
}
:args (
{ VAR
:varno 1
:varattno 2
:vartype 1802511
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 2
}
{ EXPR
:typeOid 1802511
:opType func
:oper
{ FUNC
:funcid 1802514
:funcresulttype 1802511
:funcretset false
:funcformat 0
}
:args (
{ CONST
:consttype 1007
:constlen -1
:constbyval false
:constisnull false
:constvalue 36 [ 36 0 0 0 1 0 0 0 0 0 0 0 23 0 0 0 3 0 0 0 1 0 0
0 1 0 0 0 2 0 0 0 3 0 0 0 ]
}
)
}
)
}
)
:lefttree <>
:righttree <>
:extprm ()
:locprm ()
:initplan <>
:nprm 0
:scanrelid 1
}
Seq Scan on x (cost=0.00..2826.01 rows=24334 width=8)
Filter: (z ~>= sig_in('{1,2,3}'::integer[]))
(94 rows)
--
Itai Zukerman <http://www.math-hat.com/~zukerman/;
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: GiST, Not Using Index
2003-02-27 19:37 GiST, Not Using Index Itai Zukerman <zukerman@math-hat.com>
@ 2003-02-27 20:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 21:15 ` Re: GiST, Not Using Index Itai Zukerman <zukerman@math-hat.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2003-02-27 20:51 UTC (permalink / raw)
To: Itai Zukerman <zukerman@math-hat.com>; +Cc: pgsql-sql
Itai Zukerman <zukerman@math-hat.com> writes:
> [...some definitions...]
Never leave out the "unimportant" stuff --- that's usually where you
went wrong ;-)
My guess is that you didn't supply a restriction selectivity estimator
for the ~>= operator. I forget what the default assumptions are with
no estimator, but they're probably not optimistic enough to prompt use
of an indexscan.
> [...insert 20000 rows into x...]
> Seq Scan on x (cost=0.00..2826.01 rows=24334 width=8)
> Filter: (z ~>= sig_in('{1,2,3}'::integer[]))
*How* many rows did you insert? The planner thinks 24334 will be
selected here ... one hopes the default restrictivity estimate is
less than 1.0, at least ...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: GiST, Not Using Index
2003-02-27 19:37 GiST, Not Using Index Itai Zukerman <zukerman@math-hat.com>
2003-02-27 20:51 ` Re: GiST, Not Using Index Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-02-27 21:15 ` Itai Zukerman <zukerman@math-hat.com>
2003-02-27 22:38 ` Re: GiST, Not Using Index Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Itai Zukerman @ 2003-02-27 21:15 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
OK, I've attached below the SQL I use to generate stuff and an
interactive session with the "explain" etc. The table has 40000 rows,
I do a VACUUM before doing a select, and (what I really don't
understand) the "explain verbose" doesn't mention anything about an index.
> My guess is that you didn't supply a restriction selectivity estimator
> for the ~>= operator.
I'm using contsel and contjoinsel, which I pulled from the rtree_gist
example.
Anyway, hoping you can help...
--
Itai Zukerman <http://www.math-hat.com/~zukerman/;
Attachments:
sig.sql (text/plain, 3.1K) download
-- $Id$
-- I/O functions.
CREATE FUNCTION sig_in(cstring) RETURNS sig
AS '/home/zukerman/d/pg/sig/sig', 'pg_sig_in' LANGUAGE 'C';
CREATE FUNCTION sig_in(int4) RETURNS sig
AS '/home/zukerman/d/pg/sig/sig', 'pg_sig_in_int4' LANGUAGE 'C';
CREATE FUNCTION sig_in(int4[]) RETURNS sig
AS '/home/zukerman/d/pg/sig/sig', 'pg_sig_in_int4arr' LANGUAGE 'C';
CREATE FUNCTION sig_out(sig) RETURNS cstring
AS '/home/zukerman/d/pg/sig/sig', 'pg_sig_out' LANGUAGE 'C';
-- INTERNALLENGTH = 64 bits / 8 = 8 bytes.
CREATE TYPE sig (
INTERNALLENGTH = 8,
INPUT = sig_in,
OUTPUT = sig_out
);
-- Bit functions.
CREATE FUNCTION sig_complement(sig) RETURNS sig
AS '/home/zukerman/d/pg/sig/sig', 'pg_sig_complement' LANGUAGE 'C' STRICT;
CREATE FUNCTION sig_union(sig, sig) RETURNS sig
AS '/home/zukerman/d/pg/sig/sig', 'pg_sig_union' LANGUAGE 'C' STRICT;
CREATE OPERATOR ~ (
RIGHTARG = sig,
PROCEDURE = sig_complement
);
CREATE OPERATOR ^ (
LEFTARG = sig,
RIGHTARG = sig,
PROCEDURE = sig_union,
COMMUTATOR = ^
);
-- Relations.
CREATE FUNCTION sig_equals(sig, sig) RETURNS boolean
AS '/home/zukerman/d/pg/sig/sig', 'pg_sig_equals' LANGUAGE 'C' STRICT;
CREATE FUNCTION sig_subset(sig, sig) RETURNS boolean
AS '/home/zukerman/d/pg/sig/sig', 'pg_sig_subset' LANGUAGE 'C' STRICT;
CREATE FUNCTION sig_superset(sig, sig) RETURNS boolean
AS 'SELECT sig_subset($2,$1);' LANGUAGE 'sql' STRICT;
CREATE OPERATOR ~= (
LEFTARG = sig,
RIGHTARG = sig,
PROCEDURE = sig_equals,
COMMUTATOR = ~=,
RESTRICT = eqsel,
JOIN = eqjoinsel
);
CREATE OPERATOR ~<= (
LEFTARG = sig,
RIGHTARG = sig,
PROCEDURE = sig_subset,
COMMUTATOR = ~>=,
RESTRICT = contsel,
JOIN = contjoinsel
);
CREATE OPERATOR ~>= (
LEFTARG = sig,
RIGHTARG = sig,
PROCEDURE = sig_superset,
COMMUTATOR = ~<=,
RESTRICT = contsel,
JOIN = contjoinsel
);
-- GiST definitions.
CREATE FUNCTION g_sig_consistent(internal, internal, int2) RETURNS bool
AS '/home/zukerman/d/pg/sig/sig' LANGUAGE 'C';
CREATE FUNCTION g_sig_union(bytea, internal) RETURNS internal
AS '/home/zukerman/d/pg/sig/sig' LANGUAGE 'C';
CREATE FUNCTION g_sig_compress(internal) RETURNS internal
AS '/home/zukerman/d/pg/sig/sig' LANGUAGE 'C';
CREATE FUNCTION g_sig_decompress(internal) RETURNS internal
AS '/home/zukerman/d/pg/sig/sig' LANGUAGE 'C';
CREATE FUNCTION g_sig_penalty(internal, internal) RETURNS internal
AS '/home/zukerman/d/pg/sig/sig' LANGUAGE 'C';
CREATE FUNCTION g_sig_picksplit(bytea, internal) RETURNS internal
AS '/home/zukerman/d/pg/sig/sig' LANGUAGE 'C';
CREATE FUNCTION g_sig_same(internal, internal, internal) RETURNS internal
AS '/home/zukerman/d/pg/sig/sig' LANGUAGE 'C';
CREATE OPERATOR CLASS sig_ops
FOR TYPE sig USING gist AS
OPERATOR 6 ~=,
OPERATOR 7 ~>=,
FUNCTION 1 g_sig_consistent (internal, internal, int2),
FUNCTION 2 g_sig_union (bytea, internal),
FUNCTION 3 g_sig_compress (internal),
FUNCTION 4 g_sig_decompress (internal),
FUNCTION 5 g_sig_penalty (internal, internal),
FUNCTION 6 g_sig_picksplit (bytea, internal),
FUNCTION 7 g_sig_same (internal, internal, internal);
--
CREATE TABLE x (y sig);
CREATE INDEX xx ON x USING gist (y sig_ops);
session.txt (text/plain, 3.0K) download
test=# \d x
Table "public.x"
Column | Type | Modifiers
--------+------+-----------
y | sig |
Indexes: xx gist (y sig_ops)
test=# select count(*) from x;
count
-------
40000
(1 row)
test=# vacuum full analyze x;
VACUUM
test=# select sig_in('{1,2,3,4,5}'::int4[]);
sig_in
------------------------------------------------------------------
.*****..........................................................
(1 row)
test=# select * from x where y ~>= sig_in('{1,2,3,4,5}'::int4[]);
y
------------------------------------------------------------------
******.**.*..*****...***....**.****...*.*.*.....*.**.***..*..*..
.*****.....*..............**..**......****.*....*..*.*.*.......*
******.*..***.*..*.**.**......*.**.*....*......*..****.....*...*
(3 rows)
test=# explain verbose select * from x where y ~>= sig_in('{1,2,3,4,5}'::int4[]);
QUERY PLAN
--------------------------------------------------------------------------------
{ SEQSCAN
:startup_cost 0.00
:total_cost 797.00
:rows 40
:width 8
:qptargetlist (
{ TARGETENTRY
:resdom
{ RESDOM
:resno 1
:restype 1936051
:restypmod -1
:resname y
:reskey 0
:reskeyop 0
:ressortgroupref 0
:resjunk false
}
:expr
{ VAR
:varno 1
:varattno 1
:vartype 1936051
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
}
)
:qpqual (
{ EXPR
:typeOid 16
:opType op
:oper
{ OPER
:opno 1936065
:opid 1936063
:opresulttype 16
:opretset false
}
:args (
{ VAR
:varno 1
:varattno 1
:vartype 1936051
:vartypmod -1
:varlevelsup 0
:varnoold 1
:varoattno 1
}
{ EXPR
:typeOid 1936051
:opType func
:oper
{ FUNC
:funcid 1936054
:funcresulttype 1936051
:funcretset false
:funcformat 0
}
:args (
{ CONST
:consttype 1007
:constlen -1
:constbyval false
:constisnull false
:constvalue 44 [ 44 0 0 0 1 0 0 0 0 0 0 0 23 0 0 0 5 0 0 0 1 0 0
0 1 0 0 0 2 0 0 0 3 0 0 0 4 0 0 0 5 0 0 0 ]
}
)
}
)
}
)
:lefttree <>
:righttree <>
:extprm ()
:locprm ()
:initplan <>
:nprm 0
:scanrelid 1
}
Seq Scan on x (cost=0.00..797.00 rows=40 width=8)
Filter: (y ~>= sig_in('{1,2,3,4,5}'::integer[]))
(94 rows)
test=#
* Re: GiST, Not Using Index
2003-02-27 19:37 GiST, Not Using Index Itai Zukerman <zukerman@math-hat.com>
2003-02-27 20:51 ` Re: GiST, Not Using Index Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 21:15 ` Re: GiST, Not Using Index Itai Zukerman <zukerman@math-hat.com>
@ 2003-02-27 22:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 23:12 ` Re: GiST, Not Using Index Itai Zukerman <zukerman@math-hat.com>
0 siblings, 1 reply; 5+ messages in thread
From: Tom Lane @ 2003-02-27 22:38 UTC (permalink / raw)
To: Itai Zukerman <zukerman@math-hat.com>; +Cc: pgsql-sql
Itai Zukerman <zukerman@math-hat.com> writes:
>> Never leave out the "unimportant" stuff --- that's usually where you
>> went wrong ;-)
> OK, I've attached below the SQL I use to generate stuff and an
> interactive session with the "explain" etc.
Ah. Your problem is you need to mark sig_in() as IMMUTABLE, or at least
STABLE, for an expression involving sig_in() to be considered safe to
index. If it's in C, more than likely it'd best be marked STRICT too
(else you'd better have explicit NULL checks in the function code).
Don't forget to look at your other custom functions too, to see if
they're correctly marked.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: GiST, Not Using Index
2003-02-27 19:37 GiST, Not Using Index Itai Zukerman <zukerman@math-hat.com>
2003-02-27 20:51 ` Re: GiST, Not Using Index Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 21:15 ` Re: GiST, Not Using Index Itai Zukerman <zukerman@math-hat.com>
2003-02-27 22:38 ` Re: GiST, Not Using Index Tom Lane <tgl@sss.pgh.pa.us>
@ 2003-02-27 23:12 ` Itai Zukerman <zukerman@math-hat.com>
0 siblings, 0 replies; 5+ messages in thread
From: Itai Zukerman @ 2003-02-27 23:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
> Ah. Your problem is you need to mark sig_in() as IMMUTABLE, or at
> least [...]
That was it! Thanks!
For your enjoyment:
test=# select count(*) from x;
count
--------
200000
(1 row)
test=# explain analyze select * from x where y ~>= sig_in('{1,2,3,4,5}'::int4[]);
QUERY PLAN
------------------------------------------------------------------------------------------------------
Index Scan using xx on x (cost=0.00..4.82 rows=1 width=8) (actual time=5.07..93.15 rows=29 loops=1)
Index Cond: (y ~>= '.*****..........................................................'::sig)
Total runtime: 93.31 msec
(3 rows)
test=# select * from x where y ~>= sig_in('{1,2,3,4,5}'::int4[]);
y
------------------------------------------------------------------
.*****.*..*..**.....*.*...*.*.*.....*....................*...*..
.*******.*..**.*.*.***...*....*.....**..*..*.*...**..**....**...
.*****..*...**......*.....*.....*....*..*...*...*..............*
.*****..*...**.....*....*.....*..*......**....*.......**...*....
******........*.**.....*...............**.........*.......*.....
.*****.*.**.*...***.............*...........*.**.*..*..**...*...
.******.*.*...*..*.****.*.*..**..*....**.*...***.*..*...****.**.
******....*..*.*.*.**.*..*....*..................*..*...*.......
*******..**..***.*..*...*...*.*.***.*.*...**...***.......*......
.*****..................*.*..*.*.....*...**.......*....**..**...
.******......*.*.**.*.*.*..*.....***.............**......**.....
******.*.....*.*...*......*...*.**.......*........***..*.*.....*
.******....*...*....***.*.*..*.*.....*..*......*..*.......*.....
*********.*..*.*...*...*.*.*.......................*.*....*...*.
.*****..............*.....................*....*...*...*....*..*
.*******.**.*..*....**.*.....*.....*..*.*.*..**....*...**..*...*
.*****............**..*..*.***....*...*.**.............*........
.*******...*.**.***..*.****.*..**..*....*.*.**.**.*..*........**
******.*...**..*..*.****..*...*..*.*..******.....*.*..*.**..*...
.*****.*....*..****.........*....*..*...*............**..*....**
*******.**.......*..*........*..*.***....*........**...**.....*.
******...*...........*.*.....**...**...*...**.....*...*.**...*..
.*****..............***.**.*...*....*...**.*...***......*..**...
******..**....*......**.*................*.*...*.**......*.*.**.
.*****....*.*.*...*.**..*......*...*..**.**.*...*...**....****..
.*****..*...*........*....*....*....*....................*...*..
.*****..**.*........*..*...*..*.*.*..*..*....**.*..***.*......*.
.*****...*.......................................*...........*..
*********........*.......*.......*..*.*...*....*.......*........
(29 rows)
test=# drop index xx;
DROP INDEX
test=# explain analyze select * from x where y ~>= sig_in('{1,2,3,4,5}'::int4[]);
QUERY PLAN
-----------------------------------------------------------------------------------------------
Seq Scan on x (cost=0.00..22.50 rows=1 width=8) (actual time=35.16..2231.98 rows=29 loops=1)
Filter: (y ~>= '.*****..........................................................'::sig)
Total runtime: 2232.18 msec
(3 rows)
--
Itai Zukerman <http://www.math-hat.com/~zukerman/;
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2003-02-27 23:12 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-27 19:37 GiST, Not Using Index Itai Zukerman <zukerman@math-hat.com>
2003-02-27 20:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 21:15 ` Itai Zukerman <zukerman@math-hat.com>
2003-02-27 22:38 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-27 23:12 ` Itai Zukerman <zukerman@math-hat.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