Re: oracle to psql migration - slow query in postgres

  On 10/14/2010 4:10 PM, Jon Nelson wrote:
> The first thing I'd do is think real hard about whether you really
> really want 'numeric' instead of boolean, smallint, or integer.  The
> second thing is that none of your indices (which specify a whole bunch
> of fields, by the way) have only just emailok, emailbounced, or only
> the pair of them. Without knowing the needs of your app, I would
> reconsider your index choices and go with fewer columns per index.
>
Also, make sure that the statistics is good, that histograms are large 
enough and that Geico (the genetic query optimizer) will really work 
hard to save you 15% or more on the query execution time. You can also 
make sure that any index existing index is used,  by disabling the 
sequential scan and then activating and de-activating indexes with the 
dummy expressions,  just as it was done with Oracle's rule based optimizer.
I agree that a good data model is even more crucial for Postgres than is 
the case with Oracle. Oracle, because of its rich assortment of tweaking 
& hacking tools and parameters, can be made to perform, even if the 
model is designed by someone who didn't apply the rules of good design. 
Postgres is much more susceptible to bad models and it is much harder to 
work around a badly designed model in Postgres than in Oracle. What 
people do not understand is that every application in the world will 
become badly designed after years of maintenance, adding columns, 
creating additional indexes, views, tables and triggers and than 
deploying various tools to design applications.  As noted by Murphy, 
things develop from bad to worse. Keep Postgres models simple and 
separated, because it's much easier to keep clearly defined models 
simple and effective than to keep models with 700 tables and 350 views, 
frequently with conflicting names, different columns named the same and 
same columns named differently. And monitor, monitor, monitor. Use 
strace, ltrace,  pgstatspack, auto_explain, pgfouine, pgadmin, top, sar, 
iostat and all tools you can get hold of. Without the event interface, 
it's frequently a guessing game.  It is, however, possible to manage 
things.  If working with partitioning, be very aware that PostgreSQL 
optimizer has certain problems with partitions, especially with group 
functions. If you want speed, everything must be prefixed with 
partitioning column: indexes, expressions, joins. There is no explicit 
star schema and creating hash indexes will not buy you much, as a matter 
of fact, Postgres community is extremely suspicious of the hash indexes 
and I don't see them widely used.
Having said that, I was able to solve the problems with my speed and 
partitioning.

-- 
Mladen Gogala
Sr. Oracle DBA
1500 Broadway
New York, NY 10036
(212) 329-5251
www.vmsinfo.com