Thread: poor performance in migrated database

poor performance in migrated database

From
Carlos Lopez
Date:
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



Re: poor performance in migrated database

From
Troels Arvin
Date:
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


Re: poor performance in migrated database

From
Simon Riggs
Date:
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


Re: poor performance in migrated database

From
"Scott Marlowe"
Date:
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.




Re: poor performance in migrated database

From
Troels Arvin
Date:
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


Re: [ADMIN] poor performance in migrated database

From
Carlos Lopez
Date:
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



Re: [ADMIN] poor performance in migrated database

From
Tom Lane
Date:
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

Re: [ADMIN] poor performance in migrated database

From
Carlos Lopez
Date:
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