pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedindex not used for bigint without explicit cast
8+ messages / 6 participants
[nested] [flat]
* index not used for bigint without explicit cast
@ 2003-05-27 04:07 Dave E Martin XXIII <postgresql-to.dave@dave.to>
2003-05-27 13:56 ` Re: index not used for bigint without explicit cast Rod Taylor <rbt@rbt.ca>
0 siblings, 1 reply; 8+ messages in thread
From: Dave E Martin XXIII @ 2003-05-27 04:07 UTC (permalink / raw)
To: pgsql-bugs
I could have swore there was a searchable bug database, but I can't seem
to find it, Therefore i'm assuming this is now the place.
On to the real issue:
PostgreSQL 7.3.2 on i386-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
I have a table with a bigint as its primary key. Alas, the optimizer
seems to always think a seq_scan is more efficient than an index scan on
this 170106 entry table (even after analyzing/vacuuming). This is
causing an application to take unacceptably long (it must perform
several of these insertions per second) to insert new entries into this
and another table, as the resulting referential integrity checks seem to
be taking relatively forever. Further experimention has revealed that an
index scan *will* be performed if the value in question is explicitly
converted to a bigint first. The JDBC driver (which i'm using) does not
do this however, and just puts the raw number (which I assume is correct
behaviour on its part).
I tried my application with both bigint primary keys and integer primary
keys (I did a dump and restore, massaging the datatype between, so the
application would be working with the same database)
With bigint primary keys:
processed 30 in 8233ms
processed 30 in 8482ms
processed 30 in 1748ms
processed 30 in 5072ms
processed 30 in 6142ms
processed 30 in 4529ms
With integer primary keys:
processed 30 in 1364ms
processed 30 in 1279ms
processed 30 in 704ms
processed 30 in 791ms
processed 30 in 707ms
processed 30 in 663ms
processed 30 in 753ms
processed 30 in 540ms
Unfortunately, for this application, it really needs bigint primary keys.
Below are the output from assorted analyze query experiements:
select count(*) from connection_data;
count
--------
170106
(1 row)
explain analyze select * from connection_data where id=23457;
QUERY
PLAN
--------------------------------------------------------------------------------------------------------------
Seq Scan on connection_data (cost=0.00..3716.32 rows=1 width=86)
(actual time=51.52..529.14 rows=1 loops=1)
Filter: (id = 23457)
Total runtime: 529.23 msec
(3 rows)
explain analyze select * from connection_data where id=cast (23457 as
bigint);
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Index Scan using connection_data_pkey on connection_data
(cost=0.00..3.02 rows=1 width=86) (actual time=0.41..0.42 rows=1 loops=1)
Index Cond: (id = 23457::bigint)
Total runtime: 0.49 msec
(3 rows)
select version();
version
------------------------------------------------------------------------
PostgreSQL 7.3.2 on i386-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2.3
(1 row)
Also possibly of interest:
explain analyze select * from connection_data where id=234570000000000;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------------------------------
Index Scan using connection_data_pkey on connection_data
(cost=0.00..3.02 rows=1 width=86) (actual time=44.60..44.60 rows=0 loops=1)
Index Cond: (id = 234570000000000::bigint)
Total runtime: 44.70 msec
(3 rows)
netflow=# explain analyze select * from connection_data where id=23457000;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------
Seq Scan on connection_data (cost=0.00..3716.32 rows=1 width=86)
(actual time=531.56..531.56 rows=0 loops=1)
Filter: (id = 23457000)
Total runtime: 531.64 msec
(3 rows)
Alas, even the following doesn't help:
create index connection_data_id4_tmp on connection_data(int4(id));
CREATE INDEX
netflow=# explain analyze select * from connection_data where id=23457000;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------
Seq Scan on connection_data (cost=0.00..3716.32 rows=1 width=86)
(actual time=570.67..570.67 rows=0 loops=1)
Filter: (id = 23457000)
Total runtime: 570.81 msec
(3 rows)
netflow=# analyze connection_data;
ANALYZE
netflow=# explain analyze select * from connection_data where id=23457000;
QUERY
PLAN
---------------------------------------------------------------------------------------------------------------
Seq Scan on connection_data (cost=0.00..3716.62 rows=1 width=86)
(actual time=651.57..651.57 rows=0 loops=1)
Filter: (id = 23457000)
Total runtime: 651.70 msec
(3 rows)
Even turning off enable_seqscan doesn't help. for some reason, its
convinced it can't use these indexes.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: index not used for bigint without explicit cast
2003-05-27 04:07 index not used for bigint without explicit cast Dave E Martin XXIII <postgresql-to.dave@dave.to>
@ 2003-05-27 13:56 ` Rod Taylor <rbt@rbt.ca>
0 siblings, 0 replies; 8+ messages in thread
From: Rod Taylor @ 2003-05-27 13:56 UTC (permalink / raw)
To: Dave E Martin XXIII <postgresql-to.dave@dave.to>; +Cc: pgsql-bugs
> explain analyze select * from connection_data where id=23457;
Quote the number or cast it to int8.
id='23457' or id=cast(23457 as int8)
PostgreSQL has a hard time with mixed datatypes and indexscans at the
moment.
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 8+ messages in thread
* index not used for bigint without explicit cast
@ 2023-01-18 01:43 Sam.Mesh <Sam.Mesh@gmail.com>
2023-01-18 01:55 ` Re: index not used for bigint without explicit cast Peter Geoghegan <pg@bowt.ie>
0 siblings, 1 reply; 8+ messages in thread
From: Sam.Mesh @ 2023-01-18 01:43 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org
Hello,
"index not used for bigint without explicit cast" is very old limitation
mentioned in 2003 at
https://www.postgresql.org/message-id/1054043810.93507.1.camel%40jester
Here is more recent mention of similar limitation in 2021
https://www.gojek.io/blog/the-case-s-of-postgres-not-using-index
***
The problem arrises with the other case, where the values from the table
are to be converted to match the datatype of the query (i.e., we give
numeric in query and the column is of bigint type). As Postgres can only
cast a bigint to numeric, the only option it has is to convert every row in
the table to numeric and then compare. Thus in this case, the index won’t
be used.
***
Probably, somebody knows the current state of this limitation?
Thanks,
Sam
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: index not used for bigint without explicit cast
2023-01-18 01:43 index not used for bigint without explicit cast Sam.Mesh <Sam.Mesh@gmail.com>
@ 2023-01-18 01:55 ` Peter Geoghegan <pg@bowt.ie>
2023-01-18 21:43 ` Re: index not used for bigint without explicit cast Sam.Mesh <Sam.Mesh@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Peter Geoghegan @ 2023-01-18 01:55 UTC (permalink / raw)
To: Sam.Mesh <Sam.Mesh@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org
On Tue, Jan 17, 2023 at 5:44 PM Sam.Mesh <Sam.Mesh@gmail.com> wrote:
> The problem arrises with the other case, where the values from the table are to be converted to match the datatype of the query (i.e., we give numeric in query and the column is of bigint type). As Postgres can only cast a bigint to numeric, the only option it has is to convert every row in the table to numeric and then compare. Thus in this case, the index won’t be used.
> ***
>
> Probably, somebody knows the current state of this limitation?
Not all numeric values can be converted to int8 without loss of
precision. If it was allowed, it would create subtle problems. The
same is not true for (say) int4 and int8, which can be mixed in the
way that you would expect.
Internally, int4 and int8 are part of the same btree operator family,
and so follow certain rules which are described here:
https://www.postgresql.org/docs/current/btree-behavior.html
--
Peter Geoghegan
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: index not used for bigint without explicit cast
2023-01-18 01:43 index not used for bigint without explicit cast Sam.Mesh <Sam.Mesh@gmail.com>
2023-01-18 01:55 ` Re: index not used for bigint without explicit cast Peter Geoghegan <pg@bowt.ie>
@ 2023-01-18 21:43 ` Sam.Mesh <Sam.Mesh@gmail.com>
2023-01-19 05:30 ` Re: index not used for bigint without explicit cast Noah Misch <noah@leadboat.com>
0 siblings, 1 reply; 8+ messages in thread
From: Sam.Mesh @ 2023-01-18 21:43 UTC (permalink / raw)
To: Peter Geoghegan <pg@bowt.ie>; +Cc: pgsql-bugs@lists.postgresql.org
Peter, thank you for clarification.
Could you please double check the following reasoning based on
https://www.postgresql.org/docs/current/btree-behavior.html?
- Index search by bigint column requires conversion of limiting
expressions to bigint type.
- Conversion from number(19,0) to bigint may cause overflow.
- So, index search is not possible.
On Tue, Jan 17, 2023 at 5:56 PM Peter Geoghegan <pg@bowt.ie> wrote:
>
> On Tue, Jan 17, 2023 at 5:44 PM Sam.Mesh <Sam.Mesh@gmail.com> wrote:
> > The problem arrises with the other case, where the values from the table are to be converted to match the datatype of the query (i.e., we give numeric in query and the column is of bigint type). As Postgres can only cast a bigint to numeric, the only option it has is to convert every row in the table to numeric and then compare. Thus in this case, the index won’t be used.
> > ***
> >
> > Probably, somebody knows the current state of this limitation?
>
> Not all numeric values can be converted to int8 without loss of
> precision. If it was allowed, it would create subtle problems. The
> same is not true for (say) int4 and int8, which can be mixed in the
> way that you would expect.
>
> Internally, int4 and int8 are part of the same btree operator family,
> and so follow certain rules which are described here:
>
> https://www.postgresql.org/docs/current/btree-behavior.html
>
> --
> Peter Geoghegan
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: index not used for bigint without explicit cast
2023-01-18 01:43 index not used for bigint without explicit cast Sam.Mesh <Sam.Mesh@gmail.com>
2023-01-18 01:55 ` Re: index not used for bigint without explicit cast Peter Geoghegan <pg@bowt.ie>
2023-01-18 21:43 ` Re: index not used for bigint without explicit cast Sam.Mesh <Sam.Mesh@gmail.com>
@ 2023-01-19 05:30 ` Noah Misch <noah@leadboat.com>
2023-01-19 05:51 ` Re: index not used for bigint without explicit cast Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: Noah Misch @ 2023-01-19 05:30 UTC (permalink / raw)
To: Sam.Mesh <Sam.Mesh@gmail.com>; +Cc: Peter Geoghegan <pg@bowt.ie>; pgsql-bugs@lists.postgresql.org
On Wed, Jan 18, 2023 at 01:43:43PM -0800, Sam.Mesh wrote:
> Peter, thank you for clarification.
> Could you please double check the following reasoning based on
> https://www.postgresql.org/docs/current/btree-behavior.html?
> - Index search by bigint column requires conversion of limiting
> expressions to bigint type.
> - Conversion from number(19,0) to bigint may cause overflow.
> - So, index search is not possible.
Essentially, yes. This is a query planner limitation, not a fundamental
property of the search problem. "bigintcol = '5.1'::numeric" is equivalent to
"bigintcol <> bigintcol" or "CASE WHEN bigintcol IS NULL THEN NULL ELSE false
END". In contexts that don't distinguish "false" from NULL, it's equivalent
to constant "false". Those observations apply to any search value such that
'search_value'::numeric::bigint::numeric <> 'search_value'::numeric does not
return true, including overflow-error cases. Like many datatype-specific
tricks, the planner isn't aware at this time.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: index not used for bigint without explicit cast
2023-01-18 01:43 index not used for bigint without explicit cast Sam.Mesh <Sam.Mesh@gmail.com>
2023-01-18 01:55 ` Re: index not used for bigint without explicit cast Peter Geoghegan <pg@bowt.ie>
2023-01-18 21:43 ` Re: index not used for bigint without explicit cast Sam.Mesh <Sam.Mesh@gmail.com>
2023-01-19 05:30 ` Re: index not used for bigint without explicit cast Noah Misch <noah@leadboat.com>
@ 2023-01-19 05:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-23 22:46 ` Re: index not used for bigint without explicit cast Sam.Mesh <Sam.Mesh@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2023-01-19 05:51 UTC (permalink / raw)
To: Noah Misch <noah@leadboat.com>; +Cc: Sam.Mesh <Sam.Mesh@gmail.com>; Peter Geoghegan <pg@bowt.ie>; pgsql-bugs@lists.postgresql.org
Noah Misch <noah@leadboat.com> writes:
> On Wed, Jan 18, 2023 at 01:43:43PM -0800, Sam.Mesh wrote:
>> Could you please double check the following reasoning based on
>> https://www.postgresql.org/docs/current/btree-behavior.html?
>> - Index search by bigint column requires conversion of limiting
>> expressions to bigint type.
>> - Conversion from number(19,0) to bigint may cause overflow.
>> - So, index search is not possible.
> Essentially, yes. This is a query planner limitation, not a fundamental
> property of the search problem. "bigintcol = '5.1'::numeric" is equivalent to
> "bigintcol <> bigintcol" or "CASE WHEN bigintcol IS NULL THEN NULL ELSE false
> END". In contexts that don't distinguish "false" from NULL, it's equivalent
> to constant "false". Those observations apply to any search value such that
> 'search_value'::numeric::bigint::numeric <> 'search_value'::numeric does not
> return true, including overflow-error cases. Like many datatype-specific
> tricks, the planner isn't aware at this time.
Hmm ... I don't think the planner would be the place to try to change
this. The fundamental thing to do if you wanted to improve this case
would be to invent the "bigint = numeric" operator (and its whole
family, such as "numeric = bigint", "bigint < numeric", "smallint <
numeric", etc etc) and make those part of the integer_ops opfamily.
Which'd probably lead to merging integer_ops and numeric_ops into
a single opfamily.
As far as I can think at the moment, there are not any insurmountable
semantic obstacles to doing that. There are good reasons not to try
to merge integer and float opfamilies, namely that transitivity of
equality would fail because of inexact conversions; but I can't see
how that would occur between integer-family types and numeric, with
all of those being exact types.
Nonetheless, I'm not eager to try to do it. The practical hazards
are at least two: integer vs. numeric comparison operators will be
unpleasantly slow, and adding dozens more identically-named operators
will increase the risks of getting "ambiguous operator" errors in the
parser.
In the end I'd ask why is this a problem. If you converted your
tables from Oracle-ish number(19,0) to bigint, and did not convert
your application to use bigint instead of number(19,0), that sounds
like self-inflicted damage. Do both or neither.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: index not used for bigint without explicit cast
2023-01-18 01:43 index not used for bigint without explicit cast Sam.Mesh <Sam.Mesh@gmail.com>
2023-01-18 01:55 ` Re: index not used for bigint without explicit cast Peter Geoghegan <pg@bowt.ie>
2023-01-18 21:43 ` Re: index not used for bigint without explicit cast Sam.Mesh <Sam.Mesh@gmail.com>
2023-01-19 05:30 ` Re: index not used for bigint without explicit cast Noah Misch <noah@leadboat.com>
2023-01-19 05:51 ` Re: index not used for bigint without explicit cast Tom Lane <tgl@sss.pgh.pa.us>
@ 2023-01-23 22:46 ` Sam.Mesh <Sam.Mesh@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Sam.Mesh @ 2023-01-23 22:46 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Noah Misch <noah@leadboat.com>; Peter Geoghegan <pg@bowt.ie>; pgsql-bugs@lists.postgresql.org
I've forgotten to say thanks. Thank you everybody for clarifications,
especially to Tom Lane.
On Wed, Jan 18, 2023 at 9:51 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Noah Misch <noah@leadboat.com> writes:
> > On Wed, Jan 18, 2023 at 01:43:43PM -0800, Sam.Mesh wrote:
> >> Could you please double check the following reasoning based on
> >> https://www.postgresql.org/docs/current/btree-behavior.html?
> >> - Index search by bigint column requires conversion of limiting
> >> expressions to bigint type.
> >> - Conversion from number(19,0) to bigint may cause overflow.
> >> - So, index search is not possible.
>
> > Essentially, yes. This is a query planner limitation, not a fundamental
> > property of the search problem. "bigintcol = '5.1'::numeric" is equivalent to
> > "bigintcol <> bigintcol" or "CASE WHEN bigintcol IS NULL THEN NULL ELSE false
> > END". In contexts that don't distinguish "false" from NULL, it's equivalent
> > to constant "false". Those observations apply to any search value such that
> > 'search_value'::numeric::bigint::numeric <> 'search_value'::numeric does not
> > return true, including overflow-error cases. Like many datatype-specific
> > tricks, the planner isn't aware at this time.
>
> Hmm ... I don't think the planner would be the place to try to change
> this. The fundamental thing to do if you wanted to improve this case
> would be to invent the "bigint = numeric" operator (and its whole
> family, such as "numeric = bigint", "bigint < numeric", "smallint <
> numeric", etc etc) and make those part of the integer_ops opfamily.
> Which'd probably lead to merging integer_ops and numeric_ops into
> a single opfamily.
>
> As far as I can think at the moment, there are not any insurmountable
> semantic obstacles to doing that. There are good reasons not to try
> to merge integer and float opfamilies, namely that transitivity of
> equality would fail because of inexact conversions; but I can't see
> how that would occur between integer-family types and numeric, with
> all of those being exact types.
>
> Nonetheless, I'm not eager to try to do it. The practical hazards
> are at least two: integer vs. numeric comparison operators will be
> unpleasantly slow, and adding dozens more identically-named operators
> will increase the risks of getting "ambiguous operator" errors in the
> parser.
>
> In the end I'd ask why is this a problem. If you converted your
> tables from Oracle-ish number(19,0) to bigint, and did not convert
> your application to use bigint instead of number(19,0), that sounds
> like self-inflicted damage. Do both or neither.
>
> regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2023-01-23 22:46 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-27 04:07 index not used for bigint without explicit cast Dave E Martin XXIII <postgresql-to.dave@dave.to>
2003-05-27 13:56 ` Rod Taylor <rbt@rbt.ca>
2023-01-18 01:43 index not used for bigint without explicit cast Sam.Mesh <Sam.Mesh@gmail.com>
2023-01-18 01:55 ` Peter Geoghegan <pg@bowt.ie>
2023-01-18 21:43 ` Sam.Mesh <Sam.Mesh@gmail.com>
2023-01-19 05:30 ` Noah Misch <noah@leadboat.com>
2023-01-19 05:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-01-23 22:46 ` Sam.Mesh <Sam.Mesh@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox