pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
too slow
13+ messages / 11 participants
[nested] [flat]

* too slow
@ 2005-02-08 17:08  Shashi Gireddy <shashi@cs.ua.edu>
  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

* too slow
@ 2005-02-08 17:51  Shashi Gireddy <gireddy@gmail.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 18:08  Christopher Petrilli <petrilli@gmail.com>
  parent: Shashi Gireddy <gireddy@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 18:13  Joshua D. Drake <jd@commandprompt.com>
  parent: Shashi Gireddy <gireddy@gmail.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

* Re: too slow
@ 2005-02-09 17:01  Marek Lewczuk <newsy@lewczuk.com>
  parent: Shashi Gireddy <shashi@cs.ua.edu>
  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-09 17:15  Scott Marlowe <smarlowe@g2switchworks.com>
  parent: Marek Lewczuk <newsy@lewczuk.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-03-22 14:23  Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
  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 18:44  Lee Wu <Lwu@mxlogic.com>
  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:49  Chris Hoover <revoohc@sermonaudio.com>
  parent: Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
  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: [ADMIN] Too slow
@ 2005-03-22 18:50  Thomas F.O'Connell <tfo@sitening.com>
  parent: 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 18:55  Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
  parent: Lee Wu <Lwu@mxlogic.com>
  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

* Re: Too slow
@ 2005-03-22 19:10  Sabio - PSQL <slopez_pg@ceroriesgo.co.cr>
  parent: Thomas F.O'Connell <tfo@sitening.com>
  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 19:14  Tom Lane <tgl@sss.pgh.pa.us>
  parent: Chris Hoover <revoohc@sermonaudio.com>
  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


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