Indexes bug
Hi
I found some bug on next example:
----------------------------
/* step 1 -------------- */
test=> select pubid,bn into table l1 from l;
SELECT
test=> select pubid,bn into table n1 from n;
SELECT
/* pubid & bn - int4 */
test=> select count(*) from l1;
count
-----
6776
(1 row)
test=> select count(*) from n1;
count
-----
4478
(1 row)
/* Step 2 -------------- */
test=> explain select l1.pubid from l1,n1 where l1.pubid=n1.pubid and
l1.bn=n1.bn;
NOTICE: QUERY PLAN:
Merge Join (cost=0.00 size=1 width=16)
-> Seq Scan (cost=0.00 size=0 width=0)
-> Sort (cost=0.00 size=0 width=0)
-> Seq Scan on l1 (cost=0.00 size=0 width=8)
-> Seq Scan (cost=0.00 size=0 width=0)
-> Sort (cost=0.00 size=0 width=0)
-> Seq Scan on n1 (cost=0.00 size=0 width=8)
/* it's work */
/* Step 3 -------------- */
test=> create index l1_i on l1 (pubid,bn);
CREATE
test=> explain select l1.pubid from l1,n1 where l1.pubid=n1.pubid and
l1.bn=n1.bn;
NOTICE: QUERY PLAN:
Nested Loop (cost=0.00 size=1 width=16)
-> Seq Scan on n1 (cost=0.00 size=0 width=8)
-> Index Scan using l1_i on l1 (cost=2.00 size=6776 width=8)
/* it's work */
/* Step 4 -------------- */
test=> create index n1_i on n1 (pubid,bn);
CREATE
test=> explain select l1.pubid from l1,n1 where l1.pubid=n1.pubid and
l1.bn=n1.bn;
NOTICE: QUERY PLAN:
Hash Join (cost=817.76 size=2 width=16)
-> Seq Scan on l1 (cost=267.61 size=6776 width=8)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on n1 (cost=176.77 size=4478 width=8)
/* it's not work fine :((( */
/* Step 5-------------- */
test=> drop index n1_i;
DROP
/*
test=> explain select l1.pubid from l1,n1 where l1.pubid=n1.pubid and
l1.bn=n1.bn;
NOTICE: QUERY PLAN:
Hash Join (cost=817.76 size=2 width=16)
-> Seq Scan on l1 (cost=267.61 size=6776 width=8)
-> Hash (cost=0.00 size=0 width=0)
-> Seq Scan on n1 (cost=176.77 size=4478 width=8)
/* Is it right ? I meen query plan must be like in "Step 3" ? */
----------------------------
Thnk's
SY, Serj
Serj <fenix@am.ring.ru> writes:
[ system not using an index when it should ]
Two questions:
1. Did you do a "vacuum analyze" after making the indexes?
Without that, the optimizer may be choosing a sequential scan
because it doesn't know how big the tables are.
2. What postgres version are you using?
There are some bugs in the current cvs sources that affect whether
indexes get used --- look at the ongoing threads in the hackers list.
Maybe you've found another manifestation of that problem. But if
you're using 6.3.2 then it's something different...
regards, tom lane
Import Notes
Reply to msg id not found: YourmessageofTue11Aug1998143011+040035D01D33.D27F43FC@am.ring.ru | Resolved by subject fallback
Serj <fenix@am.ring.ru> writes:
[ system not using an index when it should ]
Two questions:
1. Did you do a "vacuum analyze" after making the indexes?
Without that, the optimizer may be choosing a sequential scan
because it doesn't know how big the tables are.2. What postgres version are you using?
There are some bugs in the current cvs sources that affect whether
indexes get used --- look at the ongoing threads in the hackers list.
Maybe you've found another manifestation of that problem. But if
you're using 6.3.2 then it's something different...
I think we now know what is happening in the current cvs tree. The
optimizer calls op_class to find if there is an pg_opam entry for the
expression (int4eq), the current index access type(btree), and the
current index op class(int4_ops).
In the case of oideqint4, there is no pg_amop to match it, and we can't
add extra rows to pg_amop to make it work. I suppose we could try
adding a amopopr_compat column to pg_amop, and somehow do a lookup on
that if the first one does not match. Because of the way the system
caches are structured, we would need a new cache for that extra column,
I think. There must be a better way.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)
I think we now know what is happening in the current cvs tree. The
optimizer calls op_class to find if there is an pg_opam entry for the
expression (int4eq), the current index access type(btree), and the
current index op class(int4_ops).In the case of oideqint4, there is no pg_amop to match it, and we
can't
add extra rows to pg_amop to make it work. I suppose we could try
adding a amopopr_compat column to pg_amop, and somehow do a lookup on
that if the first one does not match. Because of the way the system
caches are structured, we would need a new cache for that extra
column,
I think. There must be a better way.
Maybe I missed it but why can't we add the extra row to pg_amop.
-DEJ
Import Notes
Resolved by subject fallback
I think we now know what is happening in the current cvs tree. The
optimizer calls op_class to find if there is an pg_opam entry for the
expression (int4eq), the current index access type(btree), and the
current index op class(int4_ops).In the case of oideqint4, there is no pg_amop to match it, and we
can't
add extra rows to pg_amop to make it work. I suppose we could try
adding a amopopr_compat column to pg_amop, and somehow do a lookup on
that if the first one does not match. Because of the way the system
caches are structured, we would need a new cache for that extra
column,
I think. There must be a better way.Maybe I missed it but why can't we add the extra row to pg_amop.
Not sure. Each access method has a predefined number of rows for that
access type. I will check on why it fails. For example, btree has five
rows. hash has fewer.
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)I think we now know what is happening in the current cvs tree. The
optimizer calls op_class to find if there is an pg_opam entry for the
expression (int4eq), the current index access type(btree), and the
current index op class(int4_ops).In the case of oideqint4, there is no pg_amop to match it, and we
can't
add extra rows to pg_amop to make it work. I suppose we could try
adding a amopopr_compat column to pg_amop, and somehow do a lookup on
that if the first one does not match. Because of the way the system
caches are structured, we would need a new cache for that extra
column,
I think. There must be a better way.Maybe I missed it but why can't we add the extra row to pg_amop.
I have checked on this, and am not sure about how to deal with it.
However, am now doubting the value of it. In the case of oid/int4, it
is clear you can call int4 for it. However, for any more complex
conversion, I don't see many multiple entries in pg_operator pointing to
the same pg_proc entry. There are some, but how much value is it going
to be to get the access method code working for these.
---------------------------------------------------------------------------
1
1 (486 rows)
1 -------------------
1 abstimeeq
1 abstimege
1 abstimegt
1 abstimele
1 abstimelt
1 abstimene
1 aclcontains
1 aclinsert
1 aclremove
29 array_eq
1 booleq
1 boolgt
1 boollt
1 boolne
1 box_above
1 box_add
1 box_below
1 box_center
1 box_contain
1 box_contained
1 box_distance
1 box_div
1 box_eq
1 box_ge
1 box_gt
1 box_intersect
1 box_le
1 box_left
1 box_lt
1 box_mul
2 box_overlap
1 box_overleft
1 box_overright
1 box_right
1 box_same
1 box_sub
1 bpchareq
1 bpcharge
1 bpchargt
1 bpcharle
1 bpcharlt
1 bpcharne
1 cash_div_flt4
1 cash_div_flt8
1 cash_div_int2
1 cash_div_int4
1 cash_eq
1 cash_ge
1 cash_gt
1 cash_le
1 cash_lt
1 cash_mi
1 cash_mul_flt4
1 cash_mul_flt8
1 cash_mul_int2
1 cash_mul_int4
1 cash_ne
1 cash_pl
1 chardiv
1 chareq
1 charge
1 chargt
1 charle
1 charlt
1 charmi
1 charmul
1 charne
1 charpl
1 circle_above
1 circle_add_pt
1 circle_below
1 circle_center
1 circle_contain
1 circle_contain_pt
1 circle_contained
1 circle_distance
1 circle_div_pt
1 circle_eq
1 circle_ge
1 circle_gt
1 circle_le
1 circle_left
1 circle_lt
1 circle_mul_pt
1 circle_ne
1 circle_overlap
1 circle_overleft
1 circle_overright
1 circle_right
1 circle_same
1 circle_sub_pt
1 close_lb
1 close_ls
1 close_lseg
1 close_pb
1 close_pl
1 close_ps
1 close_sb
1 close_sl
1 date_eq
1 date_ge
1 date_gt
1 date_le
1 date_lt
1 date_mi
1 date_mii
1 date_ne
1 date_pli
1 datetime_eq
1 datetime_ge
1 datetime_gt
1 datetime_le
1 datetime_lt
1 datetime_mi
1 datetime_mi_span
1 datetime_ne
1 datetime_pl_span
1 dcbrt
1 dexp
1 dist_cpoly
1 dist_lb
1 dist_pb
1 dist_pc
1 dist_pl
1 dist_ppath
1 dist_ps
1 dist_sb
1 dist_sl
1 dlog1
1 dpow
1 dround
1 dsqrt
1 dtrunc
1 float48div
1 float48eq
1 float48ge
1 float48gt
1 float48le
1 float48lt
1 float48mi
1 float48mul
1 float48ne
1 float48pl
1 float4abs
1 float4div
1 float4eq
1 float4ge
1 float4gt
1 float4le
1 float4lt
1 float4mi
1 float4mul
1 float4ne
1 float4pl
1 float4um
1 float84div
1 float84eq
1 float84ge
1 float84gt
1 float84le
1 float84lt
1 float84mi
1 float84mul
1 float84ne
1 float84pl
1 float8abs
1 float8div
1 float8eq
1 float8ge
1 float8gt
1 float8le
1 float8lt
1 float8mi
1 float8mul
1 float8ne
1 float8pl
1 float8um
1 flt4_mul_cash
1 flt8_mul_cash
1 ininterval
1 int24div
1 int24eq
1 int24ge
1 int24gt
1 int24le
1 int24lt
1 int24mi
1 int24mod
1 int24mul
1 int24ne
1 int24pl
1 int2_mul_cash
1 int2div
1 int2eq
1 int2ge
1 int2gt
1 int2le
1 int2lt
1 int2mi
1 int2mod
1 int2mul
1 int2ne
1 int2pl
1 int2um
1 int42div
1 int42eq
1 int42ge
1 int42gt
1 int42le
1 int42lt
1 int42mi
1 int42mod
1 int42mul
1 int42ne
1 int42pl
1 int48div
1 int48eq
1 int48ge
1 int48gt
1 int48le
1 int48lt
1 int48mi
1 int48mul
1 int48ne
1 int48pl
1 int4_mul_cash
1 int4div
1 int4eq
1 int4eqoid
2 int4fac
2 int4ge
2 int4gt
2 int4le
2 int4lt
1 int4mi
1 int4mod
1 int4mul
1 int4ne
1 int4notin
1 int4pl
1 int4um
1 int84div
1 int84eq
1 int84ge
1 int84gt
1 int84le
1 int84lt
1 int84mi
1 int84mul
1 int84ne
1 int84pl
1 int8div
1 int8eq
1 int8ge
1 int8gt
1 int8le
1 int8lt
1 int8mi
1 int8mul
1 int8ne
1 int8pl
1 int8um
1 inter_lb
1 inter_sb
1 inter_sl
2 intervalct
1 intervaleq
1 intervalge
1 intervalgt
1 intervalle
1 intervalleneq
1 intervallenge
1 intervallengt
1 intervallenle
1 intervallenlt
1 intervallenne
1 intervallt
1 intervalne
1 intervalov
1 intervalsame
1 intervalstart
1 line_distance
1 line_eq
1 line_horizontal
1 line_interpt
1 line_intersect
1 line_parallel
1 line_perp
1 line_vertical
1 lseg_center
1 lseg_distance
2 lseg_eq
1 lseg_ge
1 lseg_gt
1 lseg_horizontal
1 lseg_interpt
1 lseg_intersect
1 lseg_le
1 lseg_length
1 lseg_lt
1 lseg_parallel
1 lseg_perp
1 lseg_vertical
1 mktinterval
1 nameeq
1 namege
1 namegt
1 nameicregexeq
1 nameicregexne
1 namele
1 namelike
1 namelt
1 namene
1 namenlike
1 nameregexeq
1 nameregexne
1 oideq
1 oideqint4
1 oidint2eq
1 oidint2ge
1 oidint2gt
1 oidint2le
1 oidint2lt
1 oidint2ne
1 oidint4eq
1 oidint4ge
1 oidint4gt
1 oidint4le
1 oidint4lt
1 oidint4ne
1 oidnameeq
1 oidnamege
1 oidnamegt
1 oidnamele
1 oidnamelt
1 oidnamene
1 oidne
1 oidnotin
1 on_pb
1 on_pl
1 on_ppath
1 on_ps
1 on_sb
1 on_sl
1 oprcode
1 path_add
1 path_add_pt
1 path_center
1 path_contain_pt
1 path_distance
1 path_div_pt
1 path_inter
1 path_length
1 path_mul_pt
1 path_n_eq
1 path_n_ge
1 path_n_gt
1 path_n_le
1 path_n_lt
1 path_npoints
1 path_sub_pt
1 point_above
1 point_add
1 point_below
1 point_distance
1 point_div
1 point_eq
1 point_horiz
1 point_left
1 point_mul
1 point_right
1 point_sub
1 point_vert
1 poly_center
1 poly_contain
1 poly_contain_pt
1 poly_contained
1 poly_distance
1 poly_left
1 poly_npoints
1 poly_overlap
1 poly_overleft
1 poly_overright
1 poly_right
1 poly_same
1 pt_contained_circle
1 pt_contained_path
1 pt_contained_poly
1 reltimeeq
1 reltimege
1 reltimegt
1 reltimele
1 reltimelt
1 reltimene
1 text_ge
1 text_gt
1 text_le
1 text_lt
3 textcat
1 texteq
3 texticregexeq
3 texticregexne
3 textlike
1 textne
3 textnlike
3 textregexeq
3 textregexne
1 time_eq
1 time_ge
1 time_gt
1 time_le
1 time_lt
1 time_ne
1 timemi
1 timepl
1 timespan_div
1 timespan_eq
1 timespan_ge
1 timespan_gt
1 timespan_le
1 timespan_lt
1 timespan_mi
1 timespan_ne
1 timespan_pl
1 timespan_um
1 timestampeq
1 timestampge
1 timestampgt
1 timestample
1 timestamplt
1 timestampne
1 varchareq
1 varcharge
1 varchargt
1 varcharle
1 varcharlt
1 varcharne
--
Bruce Momjian | 830 Blythe Avenue
maillist@candle.pha.pa.us | Drexel Hill, Pennsylvania 19026
+ If your life is a hard drive, | (610) 353-9879(w)
+ Christ can be your backup. | (610) 853-3000(h)2. What postgres version are you using?
There are some bugs in the current cvs sources that affect whether
indexes get used --- look at the ongoing threads in the hackers list.
Maybe you've found another manifestation of that problem. But if
you're using 6.3.2 then it's something different...
I try it on 6.3.2 (with all patches from /pub/patches) and on current
CVS
--
SY, Serj