Roman,
> I'm really surprised that this issue doesn't pop up all the time. As
> the community grows, I think it will start to.
Actually, in the general sense of intelligent casting, the issue *does*
come up all the time. Unfortunately, this is one of those issues that
requires both an inspired solution and program-wide overhaul work to
fix. In fact, in the FTP achives you can find an alternate version of
Postgres (7.1 I think) where someone tried to fix the "stupid casting"
issue and succeeded in making Postgres crash and burn instead.
> Luckily, an unrelated post on one of the lists mentioned something
> about ANALYZE, and I realized that I had forgotten to run it after
> all the new data was imported (although I did remember a VACUUM
> FULL). After running ANALYZE, I started getting amazing
> results.....like a query that took 20 minutes last week was taking
> only 6 milliseconds now. That kicks the MSSQL server's ass all over
> the map (as I had originally expected it would!!!).
That's great!
> So things are working pretty good now....and it looks like the whole
> problem was the data type mismatch issue. I hate to point fingers,
> but the pgAdminII Migration Wizard forces all your primary keys to be
> int8 even if you set the Type Map to int4.
So? Send Dave Page (address at pgadmin.postgresql.org) a quick note
documenting the problem. I'm sure he'll patch it, or at least fix it
for PGAdmin III.
> THANK YOU to everyone on pgsql-performance for all your help. You
> are the reason that I'll be a long term member of the Postgres
> community. I hope that I can assist someone else out in the future.
You're welcome! If you can get your boss to authorize it, the
Advocacy page (advocacy.postgresql.org) could use some more business
testimonials.
-Josh Berkus