Thread: poor performance in migrated database
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
On Sat, 06 Nov 2004 11:52:15 -0800, Carlos Lopez wrote:
> 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.
--
Greetings from Troels Arvin, Copenhagen, Denmark
On Sat, 2004-11-06 at 19:52, Carlos Lopez wrote: > 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. -- Best Regards, Simon Riggs
On Sat, 2004-11-06 at 12:52, Carlos Lopez wrote: > 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.
On Sat, 06 Nov 2004 11:52:15 -0800, Carlos Lopez wrote: > 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. -- Greetings from Troels Arvin, Copenhagen, Denmark
This is one of the queries that work,and is the first
in a 4 level nested query....
where do I find how to interpret explains???
thanks in advance,
Carlos.
mate=# explain analyze select * from vdocinvdpre;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Subquery Scan vdocinvdpre (cost=265045.23..281225.66
rows=231149 width=684) (actual
time=29883.231..37652.860 rows=210073 loops=1)
-> Unique (cost=265045.23..278914.17 rows=231149
width=423) (actual time=29883.182..34109.259
rows=210073 loops=1)
-> Sort (cost=265045.23..265623.10
rows=231149 width=423) (actual
time=29883.166..31835.849 rows=210073 loops=1)
Sort Key: no_doc, seq, codigoinv, lote,
no_rollo, costo_uni, po, cantidad_total, id_pedido,
id_proveedor, udm, doc_ref, corte, id_planta, accion,
costo_total, ubicacion, cantidad_detallada,
descripcion, observaciones, factura, fecha_factura,
correlativo
-> Append (cost=36954.34..60836.63
rows=231149 width=423) (actual
time=4989.382..18277.031 rows=210073 loops=1)
-> Subquery Scan "*SELECT* 1"
(cost=36954.34..44100.17 rows=79542 width=402) (actual
time=4989.371..8786.752 rows=58466 loops=1)
-> Merge Left Join
(cost=36954.34..43304.75 rows=79542 width=402) (actual
time=4989.341..7767.335 rows=58466 loops=1)
Merge Cond:
(("outer".seq = "inner".seq) AND ("outer"."?column18?"
= "inner"."?column6?"))
-> Sort
(cost=29785.78..29925.97 rows=56076 width=366) (actual
time=2829.242..3157.807 rows=56076 loops=1)
Sort Key:
docinvdtrims.seq,
ltrim(rtrim((docinvdtrims.no_doc)::text))
-> Seq Scan on
docinvdtrims (cost=0.00..2522.76 rows=56076
width=366) (actual time=17.776..954.557 rows=56076
loops=1)
-> Sort
(cost=7168.56..7310.40 rows=56738 width=60) (actual
time=2159.854..2460.061 rows=56738 loops=1)
Sort Key:
docinvdtrimsubica.seq,
ltrim(rtrim((docinvdtrimsubica.no_doc)::text))
-> Seq Scan on
docinvdtrimsubica (cost=0.00..1327.38 rows=56738
width=60) (actual time=14.545..528.530 rows=56738
loops=1)
-> Subquery Scan "*SELECT* 2"
(cost=0.00..16736.46 rows=151607 width=423) (actual
time=7.731..7721.147 rows=151607 loops=1)
-> Seq Scan on
docinvdrollos (cost=0.00..15220.39 rows=151607
width=423) (actual time=7.699..5109.468 rows=151607
loops=1)
Total runtime: 38599.868 ms
(17 filas)
--- Simon Riggs <simon@2ndquadrant.com> wrote:
> On Sat, 2004-11-06 at 19:52, Carlos Lopez wrote:
> > 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.
>
> --
> Best Regards, Simon Riggs
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 9: the planner will ignore your desire to choose
> an index scan if your
> joining column's datatypes do not match
>
=====
___
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
Carlos Lopez <chlopezl@yahoo.com> writes:
> 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
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 --- Tom Lane <tgl@sss.pgh.pa.us> wrote: > Carlos Lopez <chlopezl@yahoo.com> writes: > > 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