Postgres and bigint
We're putting together a database design. In this design, we decided to specify all of our primary keys as
Somewhat unrelated to this, Boss came up with a question about foreign keys, wondering what the performance would be on them if we wanted to do ON DELETE CASCADE between two large tables. So I went to investigate.
My first try, to delete a row on an ON DELETE RESTRICT table that still had fk referrers, went quite fast. Seems I picked a key that was at the top of both tables. Fortunately, I thought to try a key at the bottom of both, and sure enough, it was doing a table scan.
I checked my indexes, and added one, on the fk column. Seems like it would be reasonable to have the db add one as part of the fk constraint, but maybe not.
But even with indexes, it still did a full table scan. I poked around, and found that even a pk search was doing a table scan. After a good deal more poking, I Googled, and found that indexing bigints is buggy under PostgreSQL 7.mumble (i.e. the indexes won't get used, ever), but that under 8.0 works fine. Fortunately, one of our newer servers was already running 8.0, so I tried it, and voila, worked fine.
So now I'm moving my box up to 8.0 as well.
(Minor note: I didn't track down the actual source of the problem, but was just satisfied with moving up to 8.0. Just so you know.)
bigint, as we're going to make them unique across the system, and we'll (hopefully) have more than 2^32 rows system-wide.
Somewhat unrelated to this, Boss came up with a question about foreign keys, wondering what the performance would be on them if we wanted to do ON DELETE CASCADE between two large tables. So I went to investigate.
My first try, to delete a row on an ON DELETE RESTRICT table that still had fk referrers, went quite fast. Seems I picked a key that was at the top of both tables. Fortunately, I thought to try a key at the bottom of both, and sure enough, it was doing a table scan.
I checked my indexes, and added one, on the fk column. Seems like it would be reasonable to have the db add one as part of the fk constraint, but maybe not.
But even with indexes, it still did a full table scan. I poked around, and found that even a pk search was doing a table scan. After a good deal more poking, I Googled, and found that indexing bigints is buggy under PostgreSQL 7.mumble (i.e. the indexes won't get used, ever), but that under 8.0 works fine. Fortunately, one of our newer servers was already running 8.0, so I tried it, and voila, worked fine.
So now I'm moving my box up to 8.0 as well.
(Minor note: I didn't track down the actual source of the problem, but was just satisfied with moving up to 8.0. Just so you know.)
posted by Dave at Wednesday, February 02, 2005
1 Comments:
In order to get PostgreSQL 7.x to use indexes on bigint columns you have to use quotes, something like this:
SELECT * FROM my_table WHERE my_bigint_col = '12345'
If you leave the the single quotes off of the 12345 then it won't make use of the index on bigint columns. As you noticed, this was changed in PostgreSQL 8.x. This is a common issue for folks using bigint columns, it is mentioned in the PostgreSQL docs (for 7.x) under section 8.1.1 at:
http://www.postgresql.org/docs/7.4/interactive/datatype.htmlYou've got to love those bigint's, with a max value of:
nine quintillion.
two hundred twenty-three quadrillion.
three hundred seventy-two trillion.
thirty-six billion.
eight hundred fifty-four million.
seven hundred seventy-five thousand.
eight hundred seven.
--
Joseph Scott
http://joseph.randomnetworks.com/
Post a Comment
<< Home