public inbox for [email protected]
help / color / mirror / Atom feedFAQ -- Index usage/speed
7+ messages / 4 participants
[nested] [flat]
* FAQ -- Index usage/speed
@ 2004-08-31 16:30 Thomas F.O'Connell <[email protected]>
2004-08-31 19:05 ` Re: FAQ -- Index usage/speed Bruce Momjian <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Thomas F.O'Connell @ 2004-08-31 16:30 UTC (permalink / raw)
To: [email protected]; +Cc: pgsql-docs
Bruce,
Considering the activity on the lists (at least recently and, I think,
historically) about postgres not casting (usually integer) constant
values across types, could there be a mention of this made in the FAQ?
It seems like a logical case for inclusion under 4.8:
http://www.postgresql.org/docs/faqs/FAQ.html#4.8
I was thinking something like the following:
Also note that 7.x versions of postgres will not automatically cast
constant data in certain queries such that an index would be used. For
example, if you have the following:
CREATE TABLE index_breaker (
bigintcol int8 primary key
some_data text
);
The following query is liikely to perform a sequential scan:
SELECT some_data FROM index_breaker WHERE bigintcol = 42;
postgres will interpret the constant value as a basic int and will thus
not use the index (implicitly created by the primary key) on the
bigintcol column.
There are some workarounds for this issue [per Tom Lane]:
1. Always quote your constants:
... WHERE bigintcol = '42';
Similarly, constants can be explicitly cast:
... WHERE bigintcol = int8( 42 )
2. Use a prepared statement:
PREPARE foo(bigint) AS ... WHERE bigintcol = $1;
EXECUTE foo(42);
3. Use parameterized statements in extended-query mode (essentially the
same idea as #2, but at the protocol level). This doesn't help for
pure SQL scripts, but is very workable when coding against libpq or
JDBC. Among other things it gets you out of worrying about SQL
injection attacks when your parameter values come from untrusted
sources.
Technical improvements to wording are welcome. But I think this is
worth adding to the docs somewhere.
Thanks!
-tfo
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FAQ -- Index usage/speed
2004-08-31 16:30 FAQ -- Index usage/speed Thomas F.O'Connell <[email protected]>
@ 2004-08-31 19:05 ` Bruce Momjian <[email protected]>
2004-08-31 19:12 ` Re: FAQ -- Index usage/speed Thomas F.O'Connell <[email protected]>
2004-09-01 03:11 ` Re: FAQ -- Index usage/speed Josh Berkus <[email protected]>
0 siblings, 2 replies; 7+ messages in thread
From: Bruce Momjian @ 2004-08-31 19:05 UTC (permalink / raw)
To: Thomas F. O'Connell <[email protected]>; +Cc: pgsql-docs
Yes, 4.8 would be the right spot. Not sure why we got so many reports
recently though.
However, with this fixed in 8.0, it probably isn't worth adding to the
FAQ.
---------------------------------------------------------------------------
Thomas F. O'Connell wrote:
> Bruce,
>
> Considering the activity on the lists (at least recently and, I think,
> historically) about postgres not casting (usually integer) constant
> values across types, could there be a mention of this made in the FAQ?
> It seems like a logical case for inclusion under 4.8:
>
> http://www.postgresql.org/docs/faqs/FAQ.html#4.8
>
> I was thinking something like the following:
>
> Also note that 7.x versions of postgres will not automatically cast
> constant data in certain queries such that an index would be used. For
> example, if you have the following:
>
> CREATE TABLE index_breaker (
> bigintcol int8 primary key
> some_data text
> );
>
> The following query is liikely to perform a sequential scan:
>
> SELECT some_data FROM index_breaker WHERE bigintcol = 42;
>
> postgres will interpret the constant value as a basic int and will thus
> not use the index (implicitly created by the primary key) on the
> bigintcol column.
>
> There are some workarounds for this issue [per Tom Lane]:
>
> 1. Always quote your constants:
>
> ... WHERE bigintcol = '42';
>
> Similarly, constants can be explicitly cast:
>
> ... WHERE bigintcol = int8( 42 )
>
> 2. Use a prepared statement:
>
> PREPARE foo(bigint) AS ... WHERE bigintcol = $1;
> EXECUTE foo(42);
>
> 3. Use parameterized statements in extended-query mode (essentially the
> same idea as #2, but at the protocol level). This doesn't help for
> pure SQL scripts, but is very workable when coding against libpq or
> JDBC. Among other things it gets you out of worrying about SQL
> injection attacks when your parameter values come from untrusted
> sources.
>
>
> Technical improvements to wording are welcome. But I think this is
> worth adding to the docs somewhere.
>
> Thanks!
>
> -tfo
>
--
Bruce Momjian | http://candle.pha.pa.us
[email protected] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FAQ -- Index usage/speed
2004-08-31 16:30 FAQ -- Index usage/speed Thomas F.O'Connell <[email protected]>
2004-08-31 19:05 ` Re: FAQ -- Index usage/speed Bruce Momjian <[email protected]>
@ 2004-08-31 19:12 ` Thomas F.O'Connell <[email protected]>
2004-08-31 19:17 ` Re: FAQ -- Index usage/speed Bruce Momjian <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Thomas F.O'Connell @ 2004-08-31 19:12 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: pgsql-docs
I thought about that, and it seems like the upgrade path from 7.x ->
8.x is going to be slower than among any of the 7.x releases. 8.0 is
still several months away and it will probably be several months more
before people who are affected by this issue in production databases.
The other thing I thought about was a caveat in the 7.x documentation
under indexes, as it doesn't seem to be mentioned anywhere. That would
keep it off the general FAQ but let folks using 7.x releases know about
it.
-tfo
On Aug 31, 2004, at 2:05 PM, Bruce Momjian wrote:
> Yes, 4.8 would be the right spot. Not sure why we got so many reports
> recently though.
>
> However, with this fixed in 8.0, it probably isn't worth adding to the
> FAQ.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FAQ -- Index usage/speed
2004-08-31 16:30 FAQ -- Index usage/speed Thomas F.O'Connell <[email protected]>
2004-08-31 19:05 ` Re: FAQ -- Index usage/speed Bruce Momjian <[email protected]>
2004-08-31 19:12 ` Re: FAQ -- Index usage/speed Thomas F.O'Connell <[email protected]>
@ 2004-08-31 19:17 ` Bruce Momjian <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2004-08-31 19:17 UTC (permalink / raw)
To: Thomas F.O'Connell <[email protected]>; +Cc: pgsql-docs
Thomas F.O'Connell wrote:
> I thought about that, and it seems like the upgrade path from 7.x ->
> 8.x is going to be slower than among any of the 7.x releases. 8.0 is
> still several months away and it will probably be several months more
> before people who are affected by this issue in production databases.
>
> The other thing I thought about was a caveat in the 7.x documentation
> under indexes, as it doesn't seem to be mentioned anywhere. That would
> keep it off the general FAQ but let folks using 7.x releases know about
> it.
It is on the TODO:
* -Allow SELECT * FROM tab WHERE int2col = 4 to use int2col index, int8,
float4, numeric/decimal too
--
Bruce Momjian | http://candle.pha.pa.us
[email protected] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FAQ -- Index usage/speed
2004-08-31 16:30 FAQ -- Index usage/speed Thomas F.O'Connell <[email protected]>
2004-08-31 19:05 ` Re: FAQ -- Index usage/speed Bruce Momjian <[email protected]>
@ 2004-09-01 03:11 ` Josh Berkus <[email protected]>
2004-09-01 03:21 ` Re: FAQ -- Index usage/speed Marc G. Fournier <[email protected]>
1 sibling, 1 reply; 7+ messages in thread
From: Josh Berkus @ 2004-09-01 03:11 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; Thomas F. O'Connell <[email protected]>; +Cc: pgsql-docs
Bruce,
> However, with this fixed in 8.0, it probably isn't worth adding to the
> FAQ.
I'd disagree. We can expect people to be using 7.3 and 7.4 for 2 years yet.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FAQ -- Index usage/speed
2004-08-31 16:30 FAQ -- Index usage/speed Thomas F.O'Connell <[email protected]>
2004-08-31 19:05 ` Re: FAQ -- Index usage/speed Bruce Momjian <[email protected]>
2004-09-01 03:11 ` Re: FAQ -- Index usage/speed Josh Berkus <[email protected]>
@ 2004-09-01 03:21 ` Marc G. Fournier <[email protected]>
2004-09-01 03:28 ` Re: FAQ -- Index usage/speed Bruce Momjian <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Marc G. Fournier @ 2004-09-01 03:21 UTC (permalink / raw)
To: Josh Berkus <[email protected]>; +Cc: Bruce Momjian <[email protected]>; Thomas F. O'Connell <[email protected]>; pgsql-docs
On Tue, 31 Aug 2004, Josh Berkus wrote:
> Bruce,
>
>> However, with this fixed in 8.0, it probably isn't worth adding to the
>> FAQ.
>
> I'd disagree. We can expect people to be using 7.3 and 7.4 for 2 years
> yet.
Have to agree ... I'm still supporting clients using 7.2 ...
----
Marc G. Fournier Hub.Org Networking Services (http://www.hub.org)
Email: [email protected] Yahoo!: yscrappy ICQ: 7615664
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: FAQ -- Index usage/speed
2004-08-31 16:30 FAQ -- Index usage/speed Thomas F.O'Connell <[email protected]>
2004-08-31 19:05 ` Re: FAQ -- Index usage/speed Bruce Momjian <[email protected]>
2004-09-01 03:11 ` Re: FAQ -- Index usage/speed Josh Berkus <[email protected]>
2004-09-01 03:21 ` Re: FAQ -- Index usage/speed Marc G. Fournier <[email protected]>
@ 2004-09-01 03:28 ` Bruce Momjian <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Bruce Momjian @ 2004-09-01 03:28 UTC (permalink / raw)
To: Marc G. Fournier <[email protected]>; +Cc: Josh Berkus <[email protected]>; Thomas F. O'Connell <[email protected]>; pgsql-docs
Marc G. Fournier wrote:
> On Tue, 31 Aug 2004, Josh Berkus wrote:
>
> > Bruce,
> >
> >> However, with this fixed in 8.0, it probably isn't worth adding to the
> >> FAQ.
> >
> > I'd disagree. We can expect people to be using 7.3 and 7.4 for 2 years
> > yet.
>
> Have to agree ... I'm still supporting clients using 7.2 ...
OK, added to FAQ:
<P>In pre-8.0 releases, indexes often can not be used unless the data
types exactly match the index's column types. This is particularly
true of int2, int8, and numeric column indexes.</P>
--
Bruce Momjian | http://candle.pha.pa.us
[email protected] | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2004-09-01 03:28 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-31 16:30 FAQ -- Index usage/speed Thomas F.O'Connell <[email protected]>
2004-08-31 19:05 ` Bruce Momjian <[email protected]>
2004-08-31 19:12 ` Thomas F.O'Connell <[email protected]>
2004-08-31 19:17 ` Bruce Momjian <[email protected]>
2004-09-01 03:11 ` Josh Berkus <[email protected]>
2004-09-01 03:21 ` Marc G. Fournier <[email protected]>
2004-09-01 03:28 ` Bruce Momjian <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox