Re: int8 primary keys still not using index without manual - Mailing list pgsql-general

I'm using EJB CMP (Enterprise Java Beans, Container Managed Persistence),
so the SQL is generated.  I would think this is a common usage of
PostgreSQL, as a database for a modern EJB container.  There are options
for fixing this (not including fixing postgres itself), IMHO the best is
patching the JDBC PreparedStatement code so that setLong() adds '::int8'.
The advantage here is that you can use hand coded prepared statements, as
well as auto CMP ones, and both will get the proper cast.

The real problem is that PostgreSQL out of the box is not really usable
for CMP!  This really isn't good, and I'm always suprised that it's not
fixed.  It was very luck we found the bug on the website when we were
evaluating PostgreSQL against Oracle, it wasn't easy to track down or fix,
and it causes truly horrible performance problems.

I spose you'd call it my pet peeve.

Craig


On Fri, 7 Nov 2003, Martijn van Oosterhout wrote:

> Any particular reason you can't just put the value in quotes and let
> postgres determine the type?
>
> On Fri, Nov 07, 2003 at 10:43:05AM +1100, Craig O'Shannessy wrote:
> > 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=26845437;
> >                           QUERY PLAN
> > --------------------------------------------------------------
> >  Seq Scan on lineitem  (cost=0.00..82685.91 rows=1 width=103)
> >    Filter: (lineitemid = 26845437)
> > (2 rows)
> >
> > template1=# explain select * from lineitem where lineitemid=26845437::int8;
> >                                    QUERY PLAN
> > --------------------------------------------------------------------------------
> >  Index Scan using lineitem_pkey on lineitem  (cost=0.00..3.53 rows=1 width=103)
> >    Index Cond: (lineitemid = 26845437::bigint)
> > (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
>
>