pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedtoo slow
13+ messages / 11 participants
[nested] [flat]
* too slow
@ 2005-02-08 17:08 Shashi Gireddy <shashi@cs.ua.edu>
2005-02-09 17:01 ` Re: too slow Marek Lewczuk <newsy@lewczuk.com>
0 siblings, 1 reply; 13+ messages in thread
From: Shashi Gireddy @ 2005-02-08 17:08 UTC (permalink / raw)
To: pgsql-admin
I recently migrated from MySql, The database size in mysql was 1.4GB (It is a static database). It generated a dump file (.sql) of size 8GB), It took 2days to import the whole thing into postgres. After all the response from postgres is a disaster. It took 40sec's to run a select count(logrecno) from sf10001; which generated a value 197569. And It took for ever time to display the table. How to optimize the database so that I can expect faster access to data.
each table has 70 colsX197569 rows (static data), like that I have 40 tables, Everything static.
system configuration: p4 2.8ghz 512mb ram os: xp postgres version: 8.0
thanks a million in advance,
shashi.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: too slow
2005-02-08 17:08 too slow Shashi Gireddy <shashi@cs.ua.edu>
@ 2005-02-09 17:01 ` Marek Lewczuk <newsy@lewczuk.com>
2005-02-09 17:15 ` Re: too slow Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 1 reply; 13+ messages in thread
From: Marek Lewczuk @ 2005-02-09 17:01 UTC (permalink / raw)
To: Shashi Gireddy <shashi@cs.ua.edu>; +Cc: pgsql-admin
Shashi Gireddy napisał(a):
> I recently migrated from MySql, The database size in mysql was 1.4GB (It is a static database). It generated a dump file (.sql) of size 8GB), It took 2days to import the whole thing into postgres. After all the response from postgres is a disaster. It took 40sec's to run a select count(logrecno) from sf10001; which generated a value 197569. And It took for ever time to display the table. How to optimize the database so that I can expect faster access to data.
>
> each table has 70 colsX197569 rows (static data), like that I have 40 tables, Everything static.
>
> system configuration: p4 2.8ghz 512mb ram os: xp postgres version: 8.0
First of all you should make VACUUM FULL ANALYZE for the all tables
(http://www.postgresql.org/docs/8.0/interactive/sql-vacuum.html) - this
should solve the problem. However you should also think about changing
table structure, because PostgreSQL needs different indexes than MySQL.
A few months ago I had the same problem - but after vacuuming, making
proper indexes everything is working like a charm. Believe me that you
can achieve the same speed - it is only a matter of good db structure
and environment settings
(http://www.postgresql.org/docs/8.0/interactive/runtime.html).
Regards,
ML
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: too slow
2005-02-08 17:08 too slow Shashi Gireddy <shashi@cs.ua.edu>
2005-02-09 17:01 ` Re: too slow Marek Lewczuk <newsy@lewczuk.com>
@ 2005-02-09 17:15 ` Scott Marlowe <smarlowe@g2switchworks.com>
0 siblings, 0 replies; 13+ messages in thread
From: Scott Marlowe @ 2005-02-09 17:15 UTC (permalink / raw)
To: Marek Lewczuk <newsy@lewczuk.com>; +Cc: Shashi Gireddy <shashi@cs.ua.edu>; pgsql-admin
On Wed, 2005-02-09 at 11:01, Marek Lewczuk wrote:
> Shashi Gireddy napisał(a):
> > I recently migrated from MySql, The database size in mysql was 1.4GB (It is a static database). It generated a dump file (.sql) of size 8GB), It took 2days to import the whole thing into postgres. After all the response from postgres is a disaster. It took 40sec's to run a select count(logrecno) from sf10001; which generated a value 197569. And It took for ever time to display the table. How to optimize the database so that I can expect faster access to data.
> >
> > each table has 70 colsX197569 rows (static data), like that I have 40 tables, Everything static.
> >
> > system configuration: p4 2.8ghz 512mb ram os: xp postgres version: 8.0
> First of all you should make VACUUM FULL ANALYZE for the all tables
> (http://www.postgresql.org/docs/8.0/interactive/sql-vacuum.html) - this
> should solve the problem. However you should also think about changing
> table structure, because PostgreSQL needs different indexes than MySQL.
> A few months ago I had the same problem - but after vacuuming, making
> proper indexes everything is working like a charm. Believe me that you
> can achieve the same speed - it is only a matter of good db structure
> and environment settings
> (http://www.postgresql.org/docs/8.0/interactive/runtime.html).
This user may also benefit from better normalization, as i remember him
having lots of fields in each table. Note that since PostgreSQL has to
hit the tables AFTER hitting the indexes due to MVCC, having wide,
denormalized tables with indexes is a sub-optimal solution, while
narrower tables are often much faster.
It sounded to me like what the OP was doing was more fittingly described
as "batch processing" and might well be better handled without a
database at all.
^ permalink raw reply [nested|flat] 13+ messages in thread
* too slow
@ 2005-02-08 17:51 Shashi Gireddy <gireddy@gmail.com>
2005-02-08 18:08 ` Re: too slow Christopher Petrilli <petrilli@gmail.com>
2005-02-08 18:13 ` Re: too slow Joshua D. Drake <jd@commandprompt.com>
0 siblings, 2 replies; 13+ messages in thread
From: Shashi Gireddy @ 2005-02-08 17:51 UTC (permalink / raw)
To: pgsql-admin
I recently migrated from MySql, The database size in mysql was 1.4GB
(It is a static database). It generated a dump file (.sql) of size
8GB), It took 2days to import the whole thing into postgres. After all
the response from postgres is a disaster. It took 40sec's to run a
select count(logrecno) from sf10001; which generated a value 197569.
And It took for ever time to display the table. How to optimize the
database so that I can expect faster access to data.
each table has 70 colsX197569 rows (static data), like that I have 40
tables, Everything static.
system configuration: p4 2.8ghz 512mb ram os: xp postgres version: 8.0
thanks a million in advance,
shashi.
--
Shashi Kiran Reddy. Gireddy,
Graduate Assistant,
CBER, University of Alabama.
http://www.cs.ua.edu/shashi
Home: 205-752-5137 Cell: 205-657-1438
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: too slow
2005-02-08 17:51 too slow Shashi Gireddy <gireddy@gmail.com>
@ 2005-02-08 18:08 ` Christopher Petrilli <petrilli@gmail.com>
1 sibling, 0 replies; 13+ messages in thread
From: Christopher Petrilli @ 2005-02-08 18:08 UTC (permalink / raw)
To: Shashi Gireddy <gireddy@gmail.com>; +Cc: pgsql-admin
On Tue, 8 Feb 2005 11:51:22 -0600, Shashi Gireddy <gireddy@gmail.com> wrote:
> I recently migrated from MySql, The database size in mysql was 1.4GB
> (It is a static database). It generated a dump file (.sql) of size
> 8GB), It took 2days to import the whole thing into postgres. After all
> the response from postgres is a disaster. It took 40sec's to run a
> select count(logrecno) from sf10001; which generated a value 197569.
> And It took for ever time to display the table. How to optimize the
> database so that I can expect faster access to data.
>
> each table has 70 colsX197569 rows (static data), like that I have 40
> tables, Everything static.
>
> system configuration: p4 2.8ghz 512mb ram os: xp postgres version: 8.0
A couple things...
1. Did you run VACCUM ANALYZE on the database after importing?
2. Did you use COPY or INSERT to get data into the database? THere's
an almost 100x difference.
3. What does the schema look like.
count(foo) is an expensive operation on PostgreSQL (and Oracle),
because of the multi-version system, so it's something that you
shouldn't use if you can avoid it. Other than that, I've found
PostgreSQL to be much faster than MySQL when under load, and when
mutliple people are involved, especially if there's a writer anywhere.
Chrs
--
| Christopher Petrilli
| petrilli@gmail.com
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: too slow
2005-02-08 17:51 too slow Shashi Gireddy <gireddy@gmail.com>
@ 2005-02-08 18:13 ` Joshua D. Drake <jd@commandprompt.com>
1 sibling, 0 replies; 13+ messages in thread
From: Joshua D. Drake @ 2005-02-08 18:13 UTC (permalink / raw)
To: Shashi Gireddy <gireddy@gmail.com>; +Cc: pgsql-admin
Shashi Gireddy wrote:
> I recently migrated from MySql, The database size in mysql was 1.4GB
> (It is a static database). It generated a dump file (.sql) of size
> 8GB), It took 2days to import the whole thing into postgres. ]
Well that sounds like you don't have your postgresql.conf configured
to properly utilize your system.
> After all
> the response from postgres is a disaster. It took 40sec's to run a
> select count(logrecno) from sf10001;
Are you running a RAID?
> which generated a value 197569.
> And It took for ever time to display the table. How to optimize the
> database so that I can expect faster access to data.
Have you analyzed?
>
> each table has 70 colsX197569 rows (static data), like that I have 40
> tables, Everything static.
>
Sincerely,
Joshua D. Drake
Command Prompt, Inc.
503-667-4564
> system configuration: p4 2.8ghz 512mb ram os: xp postgres version: 8.0
>
> thanks a million in advance,
> shashi.
--
Command Prompt, Inc., your source for PostgreSQL replication,
professional support, programming, managed services, shared
and dedicated hosting. Home of the Open Source Projects plPHP,
plPerlNG, pgManage, and pgPHPtoolkit.
Contact us now at: +1-503-667-4564 - http://www.commandprompt.com
Attachments:
jd.vcf (text/x-vcard, 638B) download
begin:vcard
fn:Joshua D. Drake
n:Drake;Joshua D.
org:Command Prompt, Inc.
adr:;;PO Box 215;Cascade Locks;Oregon;97014;USA
email;internet:jd@commandprompt.com
title:Consultant
tel;work:503-667-4564
tel;fax:503-210-0334
note:Command Prompt, Inc. is the largest and oldest US based commercial PostgreSQL support provider. We provide the only commercially viable integrated PostgreSQL replication solution, but also custom programming, and support. We authored the book Practical PostgreSQL, the procedural language plPHP, and adding trigger capability to plPerl.
x-mozilla-html:FALSE
url:http://www.commandprompt.com/
version:2.1
end:vcard
^ permalink raw reply [nested|flat] 13+ messages in thread
* Too slow
@ 2005-03-22 14:23 Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
2005-03-22 18:49 ` Re: Too slow Chris Hoover <revoohc@sermonaudio.com>
2005-03-22 18:50 ` Re: [ADMIN] Too slow Thomas F.O'Connell <tfo@sitening.com>
0 siblings, 2 replies; 13+ messages in thread
From: Sabio - PSQL @ 2005-03-22 14:23 UTC (permalink / raw)
To: pgsql-admin
How can I improve speed on my queries. For example this query takes one
day executing itself and it has not finalized !!!
"create table tmp_partes as select * from partes where identificacion
not in (select cedula from sujetos)"
partes have 1888000 rows, an index on identificacion
sujetos have 5500000 rows, an index on cedula
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Too slow
2005-03-22 14:23 Too slow Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
@ 2005-03-22 18:49 ` Chris Hoover <revoohc@sermonaudio.com>
2005-03-22 19:14 ` Re: Too slow Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 13+ messages in thread
From: Chris Hoover @ 2005-03-22 18:49 UTC (permalink / raw)
To: Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>; +Cc: pgsql-admin
Sabio - PSQL wrote:
> How can I improve speed on my queries. For example this query takes
> one day executing itself and it has not finalized !!!
> "create table tmp_partes as select * from partes where identificacion
> not in (select cedula from sujetos)"
>
> partes have 1888000 rows, an index on identificacion
> sujetos have 5500000 rows, an index on cedula
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
>
>
try create table tmp_partes as select * from partes where not exists
(select cedula from sujetos where cedula = partes.identificacion);
The "not in (subselect)" is very slow in postgresql.
HTH,
chris
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Too slow
2005-03-22 14:23 Too slow Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
2005-03-22 18:49 ` Re: Too slow Chris Hoover <revoohc@sermonaudio.com>
@ 2005-03-22 19:14 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2005-03-22 19:14 UTC (permalink / raw)
To: Chris Hoover <revoohc@sermonaudio.com>; +Cc: Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>; pgsql-admin
"Chris Hoover" <revoohc@sermonaudio.com> writes:
> The "not in (subselect)" is very slow in postgresql.
It's OK as long as the subselect result is small enough to hash, but
with 5500000 rows that's not going to happen :-(.
Another issue is that if there are any NULLs in the subselect then you
will probably not like the results. They are correct per spec but not
very intuitive.
Personally I'd try ye olde outer join trick:
select partes.*
from partes left join sujetos on (identificacion = cedula)
where cedula is null;
A merge join on this would likely be the most effective solution.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: [ADMIN] Too slow
2005-03-22 14:23 Too slow Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
@ 2005-03-22 18:50 ` Thomas F.O'Connell <tfo@sitening.com>
2005-03-22 19:10 ` Re: Too slow Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
1 sibling, 1 reply; 13+ messages in thread
From: Thomas F.O'Connell @ 2005-03-22 18:50 UTC (permalink / raw)
To: Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>; +Cc: PgSQL - Performance <pgsql-performance@postgresql.org>
Please post the results of that query as run through EXPLAIN ANALYZE.
Also, I'm going to reply to this on pgsql-performance, which is
probably where it better belongs.
-tfo
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-260-0005
On Mar 22, 2005, at 8:23 AM, Sabio - PSQL wrote:
> How can I improve speed on my queries. For example this query takes
> one day executing itself and it has not finalized !!!
> "create table tmp_partes as select * from partes where identificacion
> not in (select cedula from sujetos)"
>
> partes have 1888000 rows, an index on identificacion
> sujetos have 5500000 rows, an index on cedula
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Too slow
2005-03-22 14:23 Too slow Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
2005-03-22 18:50 ` Re: [ADMIN] Too slow Thomas F.O'Connell <tfo@sitening.com>
@ 2005-03-22 19:10 ` Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
0 siblings, 0 replies; 13+ messages in thread
From: Sabio - PSQL @ 2005-03-22 19:10 UTC (permalink / raw)
To: pgsql-admin
WITH: select * from partes where cedula not in (select cedula from sujetos)
Seq Scan on partes (cost=0.00..168063925339.69 rows=953831 width=109)
Filter: (NOT (subplan))
SubPlan
-> Seq Scan on sujetos (cost=0.00..162348.43 rows=5540143 width=15)
WITH: select * from partes where not exists (select cedula from sujetos
where cedula=partes.cedula)
Seq Scan on partes (cost=0.00..7373076.94 rows=953831 width=109)
Filter: (NOT (subplan))
SubPlan
-> Index Scan using sujetos_pkey on sujetos (cost=0.00..3.84
rows=1 width=15)
Index Cond: ((cedula)::text = ($0)::text)
Thomas F. O'Connell wrote:
> Please post the results of that query as run through EXPLAIN ANALYZE.
>
> Also, I'm going to reply to this on pgsql-performance, which is
> probably where it better belongs.
>
> -tfo
>
> --
> Thomas F. O'Connell
> Co-Founder, Information Architect
> Sitening, LLC
> http://www.sitening.com/
> 110 30th Avenue North, Suite 6
> Nashville, TN 37203-6320
> 615-260-0005
>
> On Mar 22, 2005, at 8:23 AM, Sabio - PSQL wrote:
>
>> How can I improve speed on my queries. For example this query takes
>> one day executing itself and it has not finalized !!!
>> "create table tmp_partes as select * from partes where identificacion
>> not in (select cedula from sujetos)"
>>
>> partes have 1888000 rows, an index on identificacion
>> sujetos have 5500000 rows, an index on cedula
>
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Too slow
@ 2005-03-22 18:44 Lee Wu <Lwu@mxlogic.com>
2005-03-22 18:55 ` Re: Too slow Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
0 siblings, 1 reply; 13+ messages in thread
From: Lee Wu @ 2005-03-22 18:44 UTC (permalink / raw)
To: Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>; pgsql-admin
PG version?
Maybe worth to try NOT EXISTS instead of NOT IN
-----Original Message-----
From: pgsql-admin-owner@postgresql.org
[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Sabio - PSQL
Sent: Tuesday, March 22, 2005 7:23 AM
To: PostgreSQL Admin
Subject: [ADMIN] Too slow
How can I improve speed on my queries. For example this query takes one
day executing itself and it has not finalized !!!
"create table tmp_partes as select * from partes where identificacion
not in (select cedula from sujetos)"
partes have 1888000 rows, an index on identificacion
sujetos have 5500000 rows, an index on cedula
---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: Too slow
2005-03-22 18:44 Re: Too slow Lee Wu <Lwu@mxlogic.com>
@ 2005-03-22 18:55 ` Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
0 siblings, 0 replies; 13+ messages in thread
From: Sabio - PSQL @ 2005-03-22 18:55 UTC (permalink / raw)
To: pgsql-admin
PG version = 8.0 over Linux Fedora Core 3
PostgreSQL was compiled from source
Lee Wu wrote:
>PG version?
>
>Maybe worth to try NOT EXISTS instead of NOT IN
>
>-----Original Message-----
>From: pgsql-admin-owner@postgresql.org
>[mailto:pgsql-admin-owner@postgresql.org] On Behalf Of Sabio - PSQL
>Sent: Tuesday, March 22, 2005 7:23 AM
>To: PostgreSQL Admin
>Subject: [ADMIN] Too slow
>
>How can I improve speed on my queries. For example this query takes one
>day executing itself and it has not finalized !!!
>"create table tmp_partes as select * from partes where identificacion
>not in (select cedula from sujetos)"
>
>partes have 1888000 rows, an index on identificacion
>sujetos have 5500000 rows, an index on cedula
>
>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 4: Don't 'kill -9' the postmaster
>
>
>
>
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2005-03-22 19:14 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-02-08 17:08 too slow Shashi Gireddy <shashi@cs.ua.edu>
2005-02-09 17:01 ` Marek Lewczuk <newsy@lewczuk.com>
2005-02-09 17:15 ` Scott Marlowe <smarlowe@g2switchworks.com>
2005-02-08 17:51 too slow Shashi Gireddy <gireddy@gmail.com>
2005-02-08 18:08 ` Christopher Petrilli <petrilli@gmail.com>
2005-02-08 18:13 ` Joshua D. Drake <jd@commandprompt.com>
2005-03-22 14:23 Too slow Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
2005-03-22 18:49 ` Re: Too slow Chris Hoover <revoohc@sermonaudio.com>
2005-03-22 19:14 ` Re: Too slow Tom Lane <tgl@sss.pgh.pa.us>
2005-03-22 18:50 ` Re: [ADMIN] Too slow Thomas F.O'Connell <tfo@sitening.com>
2005-03-22 19:10 ` Re: Too slow Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
2005-03-22 18:44 Re: Too slow Lee Wu <Lwu@mxlogic.com>
2005-03-22 18:55 ` Re: Too slow Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox