PostgreSQL JDBC - Prepared statement not using an index

Author

Prepared statement not using an index

Guido Neitzer

2005-09-01, 8:24 pm

Hi.

I have an interesting problem with the JDBC drivers. When I use a
select like this:

"SELECT t0.aktiv, t0.id, t0.ist_teilnehmer, t0.nachname, t0.plz,
t0.vorname FROM public.dga_dienstleister t0 WHERE t0.plz
like ?::varchar(256) ESCAPE '|'" withBindings: 1:"53111"(plz)>

the existing index on the plz column is not used.

When I the same select with a concrete value, the index IS used.

I use PostgreSQL 8.0.3 on Mac OS X and the JDBC driver 8.0-312 JDBC 3.

After a lot of other things, I tried using a 7.4 driver and with
this, the index is used in both cases.

Why can this happen? Is there a setting I might have not seen?
Something I do wrong?

cug

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Oliver Jowett

2005-09-01, 8:24 pm

Guido Neitzer wrote:

> I have an interesting problem with the JDBC drivers. When I use a
> select like this: [...]

> the existing index on the plz column is not used.
>
> When I the same select with a concrete value, the index IS used.

You should probably ask this on the pgsql-performance list.

> I use PostgreSQL 8.0.3 on Mac OS X and the JDBC driver 8.0-312 JDBC 3.
>
> After a lot of other things, I tried using a 7.4 driver and with this,
> the index is used in both cases.

The 8.0 drivers pass parameters individually to the backend (analogous
to using PREPARE/EXECUTE), while the 7.4 drivers do textual substitution
into the query text. This can result in different query plans as you've
discovered.

-O

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

http://archives.postgresql.org

Guido Neitzer

2005-09-02, 3:25 am

On 02.09.2005, at 0:52 Uhr, Oliver Jowett wrote:

>
> The 8.0 drivers pass parameters individually to the backend (analogous
> to using PREPARE/EXECUTE), while the 7.4 drivers do textual
> substitution
> into the query text. This can result in different query plans as
> you've
> discovered.

This sounds like a bug to me. If a simple substitution of the
placeholders with actual values ends with different query plan, my
understanding is, that there is something broken in the query
planner ...

cug

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

http://archives.postgresql.org

Jan de Visser

2005-09-02, 9:24 am

On Friday 02 September 2005 01:49, Guido Neitzer wrote:
> On 02.09.2005, at 0:52 Uhr, Oliver Jowett wrote:
>
> This sounds like a bug to me. If a simple substitution of the
> placeholders with actual values ends with different query plan, my
> understanding is, that there is something broken in the query
> planner ...

Well, no. The OP has a 'foo LIKE ?' in there. If his 'actual' query is
something like 'foo LIKE bar%', the planner is able to determine that using
an index on foo would help, whereas in the parameterized form he cannot do
that, since 'foo LIKE %bar' would not be helped by that index.

In general, things like 'LIKE ?' will be killing performance anyway, for
exactly that reason.

>
> cug

JdV!!

--
--------------------------------------------------------------
Jan de Visser _ _ _ _ _ _ _ _ _ _ jdevisser@digitalfai
rway.com

_ _ _ _ _ _ _ _ Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------

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

Dave Cramer

2005-09-04, 3:24 am

You can actually get postgres to use an index in like %bar

postgres has functional indexes so you need to create an index on
reverse(col) and then use that function in the select statement.

It's been a while, the details of actual implementation are sketchy,
perhaps the performance list would be more appropriate.

Dave
On 2-Sep-05, at 8:41 AM, Jan de Visser wrote:

> On Friday 02 September 2005 01:49, Guido Neitzer wrote:
>
>
> Well, no. The OP has a 'foo LIKE ?' in there. If his 'actual' query is
> something like 'foo LIKE bar%', the planner is able to determine
> that using
> an index on foo would help, whereas in the parameterized form he
> cannot do
> that, since 'foo LIKE %bar' would not be helped by that index.
>
> In general, things like 'LIKE ?' will be killing performance
> anyway, for
> exactly that reason.
>
>
>
> JdV!!
>
> --
> --------------------------------------------------------------
> Jan de Visser jdevisser@digitalfai
rway.com
>
> Baruk Khazad! Khazad ai-menu!
> --------------------------------------------------------------
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Sponsored Links