int8 primary keys still not using index without manual JDBC driverpatch (7.4RC1)

[Craig O'Shannessy]

Hi all,

Just thought I'd mention that I really think this problem needs to be
fixed. I

I'm patching the 7.4RC1 JDBC drivers as we speak due to this optimiser
bug, and it's the third time I've had to do this. I would think this bug
causes quite a lot of people to evaluate postgres and decide it has awful
primary key performance! I love postgres, and hate to think that this
could be happening.

template1=# explain select * from lineitem where lineitemid=2684 5437;
QUERY PLAN
--------------------------------------------------------------
Seq Scan on lineitem (cost=0.00..826 85.91 rows=1 width=103)
Filter: (lineitemid = 26845437)
(2 rows)

template1=# explain select * from lineitem where lineitemid=2684 5437::int8;
QUERY PLAN
--------------------------------------------------------------------------------
Index Scan using lineitem_pkey on lineitem (cost=0.00..3.5 3 rows=1 width=103)
Index Cond: (lineitemid = 26845437::bigin t)
(2 rows)

I've noticed this is in the TODO :
Allow SELECT * FROM tab WHERE int2col = 4 to use int2col index, int8,
float4, numeric/decimal too [optimizer])

Too hard to fix before 7.4 final?

Regards,

Craig

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

[Tom Lane]

Bruce Momjian <pg***@candle.p ha.pa.us> writes:

Here is my logic. I am having trouble getting the big picture on this:

Th big picture is that it doesn't work very well to assume that indexes
only need to handle same-datatype comparisons. I think we are
ultimately going to have to address that issue more-or-less directly.

At least for btree indexes, it doesn't seem that the index mechanics
would have too big a problem with this --- as long as you are using the
proper comparison function, who cares whether the righthand side of the
comparison is the same datatype as the left? It might be harder for
other index types, but 99.9% of the problem is with btrees anyway.
I think if we implemented it only for btrees we'd still have a usable
solution.

I'm currently digging around to see how much of the rest of the backend
really cares about it ...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postg resql.org

[Jan Wieck]

Tom Lane wrote:

Bruce Momjian <pg***@candle.p ha.pa.us> writes:

Here is my logic. I am having trouble getting the big picture on this:

Th big picture is that it doesn't work very well to assume that indexes
only need to handle same-datatype comparisons. I think we are
ultimately going to have to address that issue more-or-less directly.

At least for btree indexes, it doesn't seem that the index mechanics
would have too big a problem with this --- as long as you are using the
proper comparison function, who cares whether the righthand side of the
comparison is the same datatype as the left? It might be harder for
other index types, but 99.9% of the problem is with btrees anyway.
I think if we implemented it only for btrees we'd still have a usable
solution.

I'm currently digging around to see how much of the rest of the backend
really cares about it ...

Wouldn't that logically lead to an "abstract" operator class to be
pointed to in the original indexes operator class?

In the concrete example, int8, a btree index is using int8_ops for
opcamid 403. This operator class is specifically set up with 5 operators
designed for int8 to int8 comparision. Assume we create a pseudo
operator class that only tells the operator names "<, <=, =, >= and >",
but does not already resolve them to the operators and thus the
comparision functions and allow int8_ops to be substituted with this
pseudo operator class. In the case the planner sees a type mismatch in
the comparision, and the index operator class points to our pseudo
opclass, then it could try "on the fly" to construct the operator class
if it finds cross type operators/functions for all 5 needed operators.

Not that I know much about the planner, more asking.
Jan

--
#============== =============== =============== =============== ===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#============== =============== =============== ====== Ja******@Yahoo. com #
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Jan Wieck <Ja******@Yahoo .com> writes:

Tom Lane wrote:

Th big picture is that it doesn't work very well to assume that indexes
only need to handle same-datatype comparisons. I think we are
ultimately going to have to address that issue more-or-less directly.

Wouldn't that logically lead to an "abstract" operator class to be
pointed to in the original indexes operator class?

I've just posted a proposal in pgsql-hackers that attacks the problem
a little differently: put the cross-type operators directly into the
opclass.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postg resql.org so that your
message can get through to the mailing list cleanly

[Jan Wieck]

Tom Lane wrote:

Jan Wieck <Ja******@Yahoo .com> writes:

Tom Lane wrote:

Th big picture is that it doesn't work very well to assume that indexes
only need to handle same-datatype comparisons. I think we are
ultimately going to have to address that issue more-or-less directly.

Wouldn't that logically lead to an "abstract" operator class to be
pointed to in the original indexes operator class?

I've just posted a proposal in pgsql-hackers that attacks the problem
a little differently: put the cross-type operators directly into the
opclass.

I like that approach even better than mine. It needs less work during
the actual planning.
Jan

--
#============== =============== =============== =============== ===========#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#============== =============== =============== ====== Ja******@Yahoo. com #
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

[Tom Lane]

Jan Wieck <Ja******@Yahoo .com> writes:

I like that approach even better than mine. It needs less work during
the actual planning.

Right. I don't think I will need to touch the planner at all, except
for recording the operator strategy numbers in indexscan plan nodes.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend