David Fetter wrote:
On Tue, Nov 04, 2014 at 07:51:06AM +0900, Tatsuo Ishii wrote:
Just out of curiosity, why is Oracle's NUMBER (I assume you are
talking about this) so fast?
I suspect that what happens is that NUMBER is stored as a native type
(int2, int4, int8, int16) that depends on its
On Tue, Nov 04, 2014 at 11:44:22AM +0900, Michael Paquier wrote:
On Sun, Nov 2, 2014 at 2:30 AM, Tom Lane t...@sss.pgh.pa.us wrote:
In the case of hash indexes, because we still have to have the hash
opclasses in core, there's no way that it could be pushed out as an
extension module even
Feng Tian ft...@vitessedata.com wrote:
Performance different between Money and Numeric is *HUGE*. For
TPCH Q1, the performance difference is 5x for stock postgres, and
~20x for vitesse.
Stock postgres, for my laptop, TPCH 1G, Q1, use money type ~ 9s,
use Numeric (15, 2) is ~53s.
test=#
On 11/02/2014 06:41 PM, Tom Lane wrote:
I wrote:
Either way, requiring a dump/reload for upgrade is surely a better answer
for users of the type than just summarily screwing them.
BTW, after reflecting a bit more I'm less than convinced that this
datatype is completely useless. Even if you
On Tue, Nov 4, 2014 at 8:16 AM, Kevin Grittner kgri...@ymail.com wrote:
In any event, I'm against removing or re-deprecating the money
type. There are some problems with money; there are other problems
with numeric. If the docs are failing to make the trade-offs
clear, we should fix the
On 11/03/2014 10:11 PM, Tom Lane wrote:
Josh Berkusj...@agliodbs.com writes:
On 11/02/2014 11:41 AM, Tom Lane wrote:
Nothing that I recall at the moment, but there is certainly plenty of
stuff of dubious quality in there. I'd argue that chkpass, intagg,
intarray, isn, spi, and xml2 are all
Craig Ringer cr...@2ndquadrant.com writes:
On 11/03/2014 03:41 AM, Tom Lane wrote:
Nothing that I recall at the moment, but there is certainly plenty of
stuff of dubious quality in there. I'd argue that chkpass, intagg,
intarray, isn, spi, and xml2 are all in worse shape than the money type.
On 11/04/2014 07:33 AM, Tom Lane wrote:
More generally, it seems like a grab bag of not terribly well designed
features, and the features that do seem well designed seem like they
ought to be more generic than just for int4 arrays. So to me it feels
like proof-of-concept experimentation
On Tue, Nov 04, 2014 at 08:30:21AM +, Laurenz Albe wrote:
David Fetter wrote:
On Tue, Nov 04, 2014 at 07:51:06AM +0900, Tatsuo Ishii wrote:
Just out of curiosity, why is Oracle's NUMBER (I assume you are
talking about this) so fast?
I suspect that what happens is that NUMBER is
Am Sonntag, den 02.11.2014, 12:41 -0500 schrieb Tom Lane:
BTW, after reflecting a bit more I'm less than convinced that this
datatype is completely useless. Even if you prefer to store currency
values in numeric columns, casting to or from money provides a way to
accept or emit values in
On 11/1/14, 1:45 PM, Andrew Dunstan wrote:
On 11/01/2014 02:34 PM, Andres Freund wrote:
Yeah, if we were trying to duplicate the behavior of indisvalid, there'd
need to be a way to detect the invalid index at plan time and not use it.
But I'm not sure that that's actually an improvement from
On Mon, Nov 03, 2014 at 01:54:09PM +0100, Michael Banck wrote:
Am Sonntag, den 02.11.2014, 12:41 -0500 schrieb Tom Lane:
BTW, after reflecting a bit more I'm less than convinced that this
datatype is completely useless. Even if you prefer to store currency
values in numeric columns,
David Fetter wrote:
On Mon, Nov 03, 2014 at 01:54:09PM +0100, Michael Banck wrote:
As an additional datapoint, Vitesse Data changed the DB schema from
NUMERIC to MONEY for their TPCH benchmark for performance reasons: The
modification to data types is easy to understand -- money and double
On Sat, Nov 1, 2014 at 10:26 AM, Andres Freund and...@2ndquadrant.com
wrote:
On 2014-11-01 10:18:03 -0700, Josh Berkus wrote:
On 10/31/2014 03:07 PM, Tom Lane wrote:
I don't care one way or the other about the money type, but I will
defend
hash indexes, especially seeing that we've
On 11/3/14, 1:34 PM, Alvaro Herrera wrote:
David Fetter wrote:
On Mon, Nov 03, 2014 at 01:54:09PM +0100, Michael Banck wrote:
As an additional datapoint, Vitesse Data changed the DB schema from
NUMERIC to MONEY for their TPCH benchmark for performance reasons: The
modification to data types is
Alvaro Herrera alvhe...@2ndquadrant.com wrote:
There is a real advantage of money over numeric in the performance
front. I haven't measured it, but suffice to say that money uses
integer operations which map almost directly to CPU instructions,
whereas numeric needs to decode from our
On Tue, Nov 04, 2014 at 07:51:06AM +0900, Tatsuo Ishii wrote:
The performance of our numeric vs Oracle's was a common complaint when
I was at EnterpriseDB (in 2007).
Perhaps numeric's performance could be greatly improved in cases where
the precision is low enough to map to an
On 11/02/2014 11:41 AM, Tom Lane wrote:
Nothing that I recall at the moment, but there is certainly plenty of
stuff of dubious quality in there. I'd argue that chkpass, intagg,
intarray, isn, spi, and xml2 are all in worse shape than the money type.
Why are we holding on to xml2 again?
FWIW,
On Sun, Nov 2, 2014 at 2:30 AM, Tom Lane t...@sss.pgh.pa.us wrote:
In the case of hash indexes, because we still have to have the hash
opclasses in core, there's no way that it could be pushed out as an
extension module even if we otherwise had full support for AMs as
extensions. So what I
Josh Berkus j...@agliodbs.com writes:
On 11/02/2014 11:41 AM, Tom Lane wrote:
Nothing that I recall at the moment, but there is certainly plenty of
stuff of dubious quality in there. I'd argue that chkpass, intagg,
intarray, isn, spi, and xml2 are all in worse shape than the money type.
Why
Michael Paquier michael.paqu...@gmail.com writes:
There is as well another way: finally support WAL-logging for hash indexes.
Sure, but that's a bit easier said than done. I think Robert looked into
that a year or two back and found some stumbling blocks that it wasn't
obvious how to surmount.
Hi,
This is Feng from Vitesse. Performance different between Money and
Numeric is *HUGE*. For TPCH Q1, the performance difference is 5x for
stock postgres, and ~20x for vitesse.
Stock postgres, for my laptop, TPCH 1G, Q1, use money type ~ 9s, use
Numeric (15, 2) is ~53s.
Kevin,
test=# do
On 11/03/2014 03:41 AM, Tom Lane wrote:
Nothing that I recall at the moment, but there is certainly plenty of
stuff of dubious quality in there. I'd argue that chkpass, intagg,
intarray, isn, spi, and xml2 are all in worse shape than the money type.
What's wrong with intarray?
I'm aware of
Josh,
Do you have a list of what needs to be done to keep the MONEY type?
What is wrong with it?
Thanks,
-cktan
On Mon, Nov 3, 2014 at 10:30 PM, Feng Tian ft...@vitessedata.com wrote:
Hi,
This is Feng from Vitesse. Performance different between Money and Numeric
is *HUGE*. For TPCH Q1,
On 2014-11-01 16:59:35 -0700, Josh Berkus wrote:
All,
While there's argument about hash indexes, it looks like nobody minds if
the MONEY type goes bye-bye. So, time for a patch ...
FWIW there have been somewhat recent patches for money and it was
undeprecated not too long ago. So apparently
El nov 2, 2014 7:54 AM, Andres Freund and...@2ndquadrant.com escribió:
On 2014-11-01 16:59:35 -0700, Josh Berkus wrote:
All,
While there's argument about hash indexes, it looks like nobody minds if
the MONEY type goes bye-bye. So, time for a patch ...
FWIW there have been somewhat
On 11/02/2014 10:01 AM, Jaime Casanova wrote:
El nov 2, 2014 7:54 AM, Andres Freund and...@2ndquadrant.com
mailto:and...@2ndquadrant.com escribió:
On 2014-11-01 16:59:35 -0700, Josh Berkus wrote:
All,
While there's argument about hash indexes, it looks like nobody
minds if
the
Andrew Dunstan and...@dunslane.net writes:
On 11/02/2014 10:01 AM, Jaime Casanova wrote:
Not knowing how difficult it could be maybe a fair compromise is to
move MONEY datatype to a contrib. And documenting its limitations.
That's pretty much dead in the water, I think. Builtin types and
I wrote:
Either way, requiring a dump/reload for upgrade is surely a better answer
for users of the type than just summarily screwing them.
BTW, after reflecting a bit more I'm less than convinced that this
datatype is completely useless. Even if you prefer to store currency
values in numeric
On 11/02/2014 11:53 AM, Tom Lane wrote:
Andrew Dunstan and...@dunslane.net writes:
On 11/02/2014 10:01 AM, Jaime Casanova wrote:
Not knowing how difficult it could be maybe a fair compromise is to
move MONEY datatype to a contrib. And documenting its limitations.
That's pretty much dead in
Andrew Dunstan and...@dunslane.net writes:
On 11/02/2014 11:53 AM, Tom Lane wrote:
Well, the OID compatibility issue could be dodged by saying that we can't
do a pg_upgrade (in-place upgrade) of a database containing MONEY
columns. In fact, we might be able to just reject databases containing
On 11/02/2014 02:41 PM, Tom Lane wrote:
Andrew Dunstan and...@dunslane.net writes:
On 11/02/2014 11:53 AM, Tom Lane wrote:
Well, the OID compatibility issue could be dodged by saying that we can't
do a pg_upgrade (in-place upgrade) of a database containing MONEY
columns. In fact, we might be
Hi,
On Sat, Nov 1, 2014 at 1:21 PM, Eric Ridge eeb...@gmail.com wrote:
On Fri, Oct 31, 2014 at 6:07 PM, Tom Lane t...@sss.pgh.pa.us wrote:
I don't know if/when that will happen as such, but Simon was making noises
about writing code to treat hash indexes as unlogged automatically, which
On 10/31/2014 03:07 PM, Tom Lane wrote:
I don't care one way or the other about the money type, but I will defend
hash indexes, especially seeing that we've already added a pretty
in-your-face warning as of 9.5:
regression=# create table foo(f1 int);
CREATE TABLE
regression=# create index
On 2014-11-01 10:18:03 -0700, Josh Berkus wrote:
On 10/31/2014 03:07 PM, Tom Lane wrote:
I don't care one way or the other about the money type, but I will defend
hash indexes, especially seeing that we've already added a pretty
in-your-face warning as of 9.5:
regression=# create table
Josh Berkus j...@agliodbs.com writes:
On 10/31/2014 03:07 PM, Tom Lane wrote:
I don't care one way or the other about the money type, but I will defend
hash indexes, especially seeing that we've already added a pretty
in-your-face warning as of 9.5:
regression=# create table foo(f1 int);
On 2014-11-01 13:58:02 -0400, Tom Lane wrote:
Yeah. When we last discussed this, the difficulty was around how to make
that combination work. An unlogged index on an unlogged table is no
problem: the init-fork mechanism is able to make them both go to empty
after a crash. But for an
On 11/01/2014 01:26 PM, Andres Freund wrote:
On 2014-11-01 10:18:03 -0700, Josh Berkus wrote:
On 10/31/2014 03:07 PM, Tom Lane wrote:
I don't care one way or the other about the money type, but I will defend
hash indexes, especially seeing that we've already added a pretty
in-your-face
On 2014-11-01 14:13:02 -0400, Andrew Dunstan wrote:
Yes, although there might not be much reason to use them either. I think
Tom's first step of making hash indexes automatically unlogged makes sense.
Longer term I'd like to see unlogged as an option for all AMs - especially
btree.
If we
On 11/01/2014 01:58 PM, Tom Lane wrote:
Andres Freund and...@2ndquadrant.com writes:
On 2014-11-01 10:18:03 -0700, Josh Berkus wrote:
Yes, and I'm arguing that is the wrong decision. If hash indexes are
discouraged, then they shouldn't be in core in the first place.
Last time we discussed
Andres Freund and...@2ndquadrant.com writes:
On 2014-11-01 13:58:02 -0400, Tom Lane wrote:
maybe we don't have to. What about having the init-fork mechanism restore
a hash index into a state with the metapage marked as invalid? hashinsert
etc could simply do nothing when they see this
On 2014-11-01 14:19:22 -0400, Andrew Dunstan wrote:
Isn't the planner still going to try to use the index in that case? If it's
not then I'd be OK with it, but if it's going to make the table largely
unusable until it's reindexed that would be rather sad.
Both the planner (for querying) and
Andres Freund and...@2ndquadrant.com writes:
On 2014-11-01 14:19:22 -0400, Andrew Dunstan wrote:
Isn't the planner still going to try to use the index in that case? If it's
not then I'd be OK with it, but if it's going to make the table largely
unusable until it's reindexed that would be
On 2014-11-01 14:23:07 -0400, Tom Lane wrote:
Andres Freund and...@2ndquadrant.com writes:
On 2014-11-01 13:58:02 -0400, Tom Lane wrote:
maybe we don't have to. What about having the init-fork mechanism restore
a hash index into a state with the metapage marked as invalid? hashinsert
Andres Freund and...@2ndquadrant.com writes:
A REINDEX is imo unlikely to be acceptable. It takes long (why would you
bother on a small table?) and locks the relation/indexes.
I think the goalposts just took a vacation to Acapulco.
What exactly do you think is going to make a crashed unlogged
On 2014-11-01 14:39:21 -0400, Tom Lane wrote:
Andres Freund and...@2ndquadrant.com writes:
A REINDEX is imo unlikely to be acceptable. It takes long (why would you
bother on a small table?) and locks the relation/indexes.
I think the goalposts just took a vacation to Acapulco.
I think
On 11/01/2014 02:34 PM, Andres Freund wrote:
Yeah, if we were trying to duplicate the behavior of indisvalid, there'd
need to be a way to detect the invalid index at plan time and not use it.
But I'm not sure that that's actually an improvement from the user's
standpoint: what they'd see is
Andres Freund and...@2ndquadrant.com writes:
On 2014-11-01 14:39:21 -0400, Tom Lane wrote:
What exactly do you think is going to make a crashed unlogged index valid
again without a REINDEX? Certainly the people who are currently using
hash indexes in the way Andrew describes are expecting to
On 2014-11-01 14:48:20 -0400, Tom Lane wrote:
Andres Freund and...@2ndquadrant.com writes:
On 2014-11-01 14:39:21 -0400, Tom Lane wrote:
What exactly do you think is going to make a crashed unlogged index valid
again without a REINDEX? Certainly the people who are currently using
hash
On 11/01/2014 02:39 PM, Tom Lane wrote:
Andres Freund and...@2ndquadrant.com writes:
A REINDEX is imo unlikely to be acceptable. It takes long (why would you
bother on a small table?) and locks the relation/indexes.
I think the goalposts just took a vacation to Acapulco.
What exactly do you
On 2014-11-01 14:56:35 -0400, Andrew Dunstan wrote:
On 11/01/2014 02:39 PM, Tom Lane wrote:
Andres Freund and...@2ndquadrant.com writes:
A REINDEX is imo unlikely to be acceptable. It takes long (why would you
bother on a small table?) and locks the relation/indexes.
I think the goalposts
Andres Freund and...@2ndquadrant.com writes:
On 2014-11-01 14:48:20 -0400, Tom Lane wrote:
Sure. And as long as you aren't issuing queries that would want to scan
the crashed index, it won't matter either way. The question is whether
you'd rather that your inessential reporting queries fail
Andrew Dunstan and...@dunslane.net writes:
The real question here is whether the table should continue to be usable
in a degraded state until it's reindexed.
It certainly will be, as long as your notion of usable in a degraded
state doesn't include issuing queries that would prefer to use the
Andres Freund and...@2ndquadrant.com writes:
One argument in that direction imo is HS. We certainly would just
generally ignore unlogged indexes for querying while InRecovery, right?
Because otherwise HS would become pretty useless. And I think it'd be
pretty wierd if things worked on HS and
On 2014-11-01 15:11:40 -0400, Tom Lane wrote:
Andres Freund and...@2ndquadrant.com writes:
One argument in that direction imo is HS. We certainly would just
generally ignore unlogged indexes for querying while InRecovery, right?
Because otherwise HS would become pretty useless. And I think
Andres Freund and...@2ndquadrant.com writes:
On 2014-11-01 15:11:40 -0400, Tom Lane wrote:
I don't see how HS has anything to do with this discussion.
Consider:
SELECT * FROM tbl WHERE active AND value = $1;
that can be satisfied by two indexes. One on (value), and an unlogged
index on
On 2014-11-01 15:33:27 -0400, Tom Lane wrote:
With current usage of hash indexes, nobody would ever construct such an
arrangement
Do you think this should only be implemented for hash indexes? I'd think
we'd do it for all existing index AMs?
I'm not all that excited about unlogged hash
Andres Freund and...@2ndquadrant.com writes:
On 2014-11-01 15:33:27 -0400, Tom Lane wrote:
With current usage of hash indexes, nobody would ever construct such an
arrangement
Do you think this should only be implemented for hash indexes? I'd think
we'd do it for all existing index AMs?
I
On Sat, Nov 1, 2014 at 12:40 PM, Andres Freund and...@2ndquadrant.com wrote:
I'm not all that excited about unlogged hash indexes. Yes, that'll
remove a annoying hazard, but I probably won't use them anyway. I am
somewhat excited about the more general unlogged indexes feature.
I strongly
All,
While there's argument about hash indexes, it looks like nobody minds if
the MONEY type goes bye-bye. So, time for a patch ...
--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your
On Sun, Nov 2, 2014 at 12:59 PM, Josh Berkus j...@agliodbs.com wrote:
All,
While there's argument about hash indexes, it looks like nobody minds if
the MONEY type goes bye-bye. So, time for a patch ...
Will the patch move the feature to a contrib module?
Or will our release notes state
PostgreSQL has two bits of obsolete, incomplete functionality which
entrap and frustrate new users in large numbers. Both of these features
share the following characteristics:
* added more than 10 years ago
* have the same names as useful features from other databases
* were never finished and
Josh Berkus j...@agliodbs.com writes:
Of course, I'm talking about the MONEY type and hash indexes (not the
hash ops class, which is useful, just the index type). It's time to put
both of these features out to pasture. Certainly neither of theise
features would be accepted into PostgreSQL
On Fri, Oct 31, 2014 at 6:07 PM, Tom Lane t...@sss.pgh.pa.us wrote:
I don't know if/when that will happen as such, but Simon was making noises
about writing code to treat hash indexes as unlogged automatically, which
would more or less fix the worst risks. That's not just a special case
for
64 matches
Mail list logo
