poor performance in migrated database

[Carlos Lopez]

I have migrated a database from MS SQL to a
postgresSQL database, but when running it, the results
are very slow (and unusable) which is the only reason
we don't entirely move to postgresSQL.
The problem is that there are many nested views which
normally join tables by using two fields, one
character and other integer.
The biggest table has about 300k records (isn't it too
little for having performance problems?)
What could be the poor performance reason? the server
is a dual itanium (intel 64bits) processor with 6Gb of
RAM and a 36Gb Raid 5 scsi hdds of 15k rpm.
If someone has the time and wants to check the
structure, I have a copy of everything at
http://www.micredito.com.sv/.carlos/materiales.sql.bz2
it is a pgsqldump made with postgres 7.4
Thanks in advance for your help.

Carlos Lopez Linares.


=====
___
Ing. Carlos L�pez Linares
IT Consultant
Quieres aprender linux?
visita http://www.aprende-linux.com.sv



__________________________________
Do you Yahoo!?
Check out the new Yahoo! Front Page.
www.yahoo.com



---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

[Troels Arvin]

Post by Carlos Lopez
I have migrated a database from MS SQL to a
postgresSQL database, but when running it, the results
are very slow (and unusable) which is the only reason
we don't entirely move to postgresSQL.

Have you run ANALYZE lately? (See manual.)

Do you know how to use EXPLAIN? (See manual.) If so: Please post an
example query which is slow, and the corresponding output from EXPLAIN.

Have you tried turning your random_page_cost a bit down? (My experience
its value should generally be lessened.)

Have you read
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html ?

Post by Carlos Lopez
The biggest table has about 300k records (isn't it too
little for having performance problems?)

That should be no problem.

[Troels Arvin]

I have migrated a database from MS SQL to a postgresSQL database, but
when running it, the results are very slow (and unusable) which is the
only reason we don't entirely move to postgresSQL.

Have you run ANALYZE lately? (See manual.)

Do you know how to use EXPLAIN? (See manual.) If so: Please post an
example query which is slow, and the corresponding output from EXPLAIN.

Have you tried turning your random_page_cost a bit down? (My experience
its value should generally be lessened.)

Have you read
http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html ?

The biggest table has about 300k records (isn't it too little for having
performance problems?)

That should be no problem.

[Simon Riggs]

Post by Carlos Lopez
The problem is that there are many nested views which
normally join tables by using two fields, one
character and other integer.

PostgreSQL has difficulty with some multi-column situations, even though
in general it has a particularly good query optimizer.

If the first column is poorly selective, yet the addition of the second
column makes the combination very highly selective then PostgreSQL may
not be able to realise this, ANALYZE or not. ANALYZE doesn't have
anywhere to store multi-column selectivity statistics.

EXPLAIN ANALYZE will show you whether this is the case. It seems likely
that the estimated cardinality of certain joins is incorrect.

[Scott Marlowe]

Post by Carlos Lopez
I have migrated a database from MS SQL to a
postgresSQL database, but when running it, the results
are very slow (and unusable) which is the only reason
we don't entirely move to postgresSQL.
The problem is that there are many nested views which
normally join tables by using two fields, one
character and other integer.

If you are joining on different type fields, you might find the query
planner encouraged to use the indexes if you cast one field to the other
field's type. If that's possible.




---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ***@postgresql.org

[Tom Lane]

This is one of the queries that work,and is the first
in a 4 level nested query....

Do you really need UNION (as opposed to UNION ALL) in this query?
The EXPLAIN shows that almost half the runtime is going into the
sort/uniq to eliminate duplicates ... and according to the row
counts, there are no duplicates, so it's wasted effort.

I looked at your schema and saw an awful lot of SELECT DISTINCTs
that looked like they might not be necessary, too. But I'm not
willing to crawl through 144 views with no information about
which ones are causing you problems. What's a typical query
that you are unsatisfied with the performance of?

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

[Carlos Lopez]

Dear Tom,
thanks for your information.
Where can I learn more about the explain and analyze??
One view that is giving a lot of problems is vkardex_3
which is used most of the time...
The explain analyze I sent is one of the views that
conform this one.

Thanks in advance.
Carlos Lopez Linares

Post by Tom Lane

This is one of the queries that work,and is the

first

in a 4 level nested query....

Do you really need UNION (as opposed to UNION ALL)
in this query?
The EXPLAIN shows that almost half the runtime is
going into the
sort/uniq to eliminate duplicates ... and according
to the row
counts, there are no duplicates, so it's wasted
effort.
I looked at your schema and saw an awful lot of
SELECT DISTINCTs
that looked like they might not be necessary, too.
But I'm not
willing to crawl through 144 views with no
information about
which ones are causing you problems. What's a
typical query
that you are unsatisfied with the performance of?
regards, tom lane

=====
___
Ing. Carlos L�pez Linares
IT Consultant
Quieres aprender linux?
visita http://www.aprende-linux.com.sv

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly