Re: Why Not MySQL?

"Mitch Vincent" <mitch(at)huntsvilleal(dot)com> writes:
> applicants_states is a table having only two fields --> app_id int4, rstate
> varchar(2) -- this holds all the state abbreviations of the states that an
> applicant will relocate too.

Ah, got it. If we had better search capabilities on arrays, you could
have stored this within the applicants table as an array of char(2) ...
but as is, I suspect you did the right thing to make it a second table.

>> It'd be fairly reasonable for the system to use either hash or merge
>> join, I think. What does EXPLAIN show that the system is actually
>> doing with this query?

> 6.5.3 : [ nested loop ]
> 7.0RC3 (with the exact same data) : [ merge join ]

So, may I have the envelope please? What's the timings?

> I did get two errors importing data from the 6.5.3 database into the 7.0
> database :

> ERROR: DefineIndex: opclass "date_ops" does not accept datatype "timestamp"
> ERROR: DefineIndex: opclass "date_ops" does not accept datatype "timestamp"

> And I have several fields of type 'datetime' in 6.5.3 which seem to get
> translated to 'timestamp' in 7.0 -- then I try to index them using
> date_ops..

Oh, that's interesting. I doubt that date_ops will work very well on
timestamp data (or on its predecessor datetime). But 7.0 is the first
version that actually checks whether your requested index operators are
compatible with the column datatype --- previous versions would blindly
do what you told them to, and very possibly coredump depending on what
the datatypes in question where. I wonder if that mistake was causing
some of the instability you had with 6.5?

regards, tom lane