pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedPostgresql performance
16+ messages / 14 participants
[nested] [flat]
* Postgresql performance
@ 2003-03-12 20:47 Enix Empreendimentos e Construções Ltda. <contato@enix.com.br>
2003-03-12 21:38 ` Re: Postgresql performance Chad Thompson <chad@weblinkservices.com>
2003-03-12 21:45 ` Re: Postgresql performance Rod Taylor <rbt@rbt.ca>
2003-03-13 22:26 ` Re: Postgresql performance Hannu Krosing <hannu@tm.ee>
0 siblings, 3 replies; 16+ messages in thread
From: Enix Empreendimentos e Construções Ltda. @ 2003-03-12 20:47 UTC (permalink / raw)
To: pgsql-performance
Hi everybody.
I am a newbie to Postgresql, trying to migrate an application from MSAccess.
I am quite dissapointed with the problems I am facing with some queries
containing multiple joins. I confess it has been hard for someone that is
not a DBA to figure out which are the problems. Just to ilustrate, I have
some queries that provide a reasonable query plan (at least from my point of
view), but that return no result: keep running on and on.
My system description is: Postgresql 7.1.3, Linux RedHat 7.1 (all patches
applied), 160Mb RAM.
Is the performance of the mentioned Postgresql version much slower than the
7.3.1?
All advice will be more than appeciated.
Regards.
Fabio
---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.461 / Virus Database: 260 - Release Date: 10/03/2003
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Postgresql performance
2003-03-12 20:47 Postgresql performance Enix Empreendimentos e Construções Ltda. <contato@enix.com.br>
@ 2003-03-12 21:38 ` Chad Thompson <chad@weblinkservices.com>
2 siblings, 0 replies; 16+ messages in thread
From: Chad Thompson @ 2003-03-12 21:38 UTC (permalink / raw)
To: Enix Empreendimentos e Construções Ltda. <contato@enix.com.br>; pgsql-performance
There are a lot of things that can be done to speed up your queries.
Especially if you are using the kludge that Access puts out. Post some of
them and we can help.
As far as the difference between 7.1.3 and 7.3.2 there are a lot of
optimizations as well as bug fixes, its always a good idea to upgrade.
HTH
Chad
----- Original Message -----
From: "Enix Empreendimentos e Construções Ltda." <contato@enix.com.br>
To: <pgsql-performance@postgresql.org>
Sent: Wednesday, March 12, 2003 1:47 PM
Subject: [PERFORM] Postgresql performance
> Hi everybody.
>
> I am a newbie to Postgresql, trying to migrate an application from
MSAccess.
>
> I am quite dissapointed with the problems I am facing with some queries
> containing multiple joins. I confess it has been hard for someone that is
> not a DBA to figure out which are the problems. Just to ilustrate, I have
> some queries that provide a reasonable query plan (at least from my point
of
> view), but that return no result: keep running on and on.
>
> My system description is: Postgresql 7.1.3, Linux RedHat 7.1 (all patches
> applied), 160Mb RAM.
>
> Is the performance of the mentioned Postgresql version much slower than
the
> 7.3.1?
>
> All advice will be more than appeciated.
>
> Regards.
>
> Fabio
>
>
> ---
> Outgoing mail is certified Virus Free.
> Checked by AVG anti-virus system (http://www.grisoft.com).
> Version: 6.0.461 / Virus Database: 260 - Release Date: 10/03/2003
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html
>
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Postgresql performance
2003-03-12 20:47 Postgresql performance Enix Empreendimentos e Construções Ltda. <contato@enix.com.br>
@ 2003-03-12 21:45 ` Rod Taylor <rbt@rbt.ca>
2 siblings, 0 replies; 16+ messages in thread
From: Rod Taylor @ 2003-03-12 21:45 UTC (permalink / raw)
To: Enix Empreendimentos e Construções "Ltda." <contato@enix.com.br>; +Cc: pgsql-performance
> Is the performance of the mentioned Postgresql version much slower than the
> 7.3.1?
Somewhat, but not significantly.
Standard questions:
Have you run VACUUM?
Have you run ANALYZE?
What does EXPLAIN ANALYZE <query> output for the slow queries?
If performance is still poor after the first 2, send the results of
EXPLAIN here and we'll tell you which index you're missing ;)
--
Rod Taylor <rbt@rbt.ca>
PGP Key: http://www.rbt.ca/rbtpub.asc
Attachments:
signature.asc (application/pgp-signature, 186B) download
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Postgresql performance
2003-03-12 20:47 Postgresql performance Enix Empreendimentos e Construções Ltda. <contato@enix.com.br>
@ 2003-03-13 22:26 ` Hannu Krosing <hannu@tm.ee>
2 siblings, 0 replies; 16+ messages in thread
From: Hannu Krosing @ 2003-03-13 22:26 UTC (permalink / raw)
To: Enix Empreendimentos e Construções "Ltda." <contato@enix.com.br>; +Cc: pgsql-performance
Enix Empreendimentos e Construções Ltda. kirjutas K, 12.03.2003 kell
22:47:
> Hi everybody.
>
> I am a newbie to Postgresql, trying to migrate an application from MSAccess.
>
> I am quite dissapointed with the problems I am facing with some queries
> containing multiple joins.
Postgres currently does *not* optimize join order for explicit joins
(this is currently left as a cludge for users to hand-optimize query
plans).
To get the benefits form optimiser you have to rewrite
FROM TA JOIN TB ON TA.CB=TB.CB
to
FROM A,B
WHERE TA.CB=TB.CB
> I confess it has been hard for someone that is
> not a DBA to figure out which are the problems. Just to ilustrate, I have
> some queries that provide a reasonable query plan (at least from my point of
> view), but that return no result: keep running on and on.
Could you try to explain it in other words (or give an example). I am
not native english speaker and I can read your text in at least 5
different ways ;(
> Is the performance of the mentioned Postgresql version much slower than the
> 7.3.1?
It can be slower. It may also be a little faster in some very specific
cases ;)
--------------
Hannu
^ permalink raw reply [nested|flat] 16+ messages in thread
* Postgresql performance
@ 2003-10-22 10:25 CHEWTC@ap.nec.com.sg
2003-10-22 14:03 ` Re: Postgresql performance Tom Lane <tgl@sss.pgh.pa.us>
2003-10-22 16:06 ` Re: Postgresql performance Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 16+ messages in thread
From: CHEWTC@ap.nec.com.sg @ 2003-10-22 10:25 UTC (permalink / raw)
To: pgsql-performance
Hi
Currently we are running Postgresql v7.3.2 on Redhat Linux OS v9.0. We have
Windows2000 client machines inserting records into the Postgresql tables
via ODBC.
After a few weeks of usage, when we do a \d at the sql prompt, there was a
duplicate object name, ie it can be a duplicate row of index or table.
When we do a \d table_name, it will show a duplication of column names
inside the table.
It doesnt affect the insertion/updating of the tables, but when we do a
pg_dump -Da -t <table_name> <db_name> > /exp/<table_name>.sql, it will not
do a proper backup/dump.
Do we need to apply any patches or maintenace?
Please be informed that NEC Singapore Pte Ltd is now known as NEC Solutions
Asia Pacific Pte Ltd.
Our address and contact numbers remain.
Email: chewtc@ap.nec.com.sg
http://www.nec.com.sg/ap
Thank you,
REgards.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Postgresql performance
2003-10-22 10:25 Postgresql performance CHEWTC@ap.nec.com.sg
@ 2003-10-22 14:03 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 16+ messages in thread
From: Tom Lane @ 2003-10-22 14:03 UTC (permalink / raw)
To: CHEWTC@ap.nec.com.sg; +Cc: pgsql-performance
CHEWTC@ap.nec.com.sg writes:
> Currently we are running Postgresql v7.3.2 on Redhat Linux OS v9.0. We have
> Windows2000 client machines inserting records into the Postgresql tables
> via ODBC.
> After a few weeks of usage, when we do a \d at the sql prompt, there was a
> duplicate object name, ie it can be a duplicate row of index or table.
> When we do a \d table_name, it will show a duplication of column names
> inside the table.
Are you sure you are using 7.3 psql? This sounds like something that
could happen with a pre-7.3 (not schema aware) psql, if there are
multiple occurrences of the same table name in different schemas.
> It doesnt affect the insertion/updating of the tables, but when we do a
> pg_dump -Da -t <table_name> <db_name> > /exp/<table_name>.sql, it will not
> do a proper backup/dump.
I'd wonder about whether you have the right pg_dump, too.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Postgresql performance
2003-10-22 10:25 Postgresql performance CHEWTC@ap.nec.com.sg
@ 2003-10-22 16:06 ` Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 16+ messages in thread
From: Josh Berkus @ 2003-10-22 16:06 UTC (permalink / raw)
To: CHEWTC@ap.nec.com.sg; pgsql-performance
NEC,
> After a few weeks of usage, when we do a \d at the sql prompt, there was a
> duplicate object name, ie it can be a duplicate row of index or table.
> When we do a \d table_name, it will show a duplication of column names
> inside the table.
I think the version of PSQL and pg_dump which you are using do not match the
back-end database version. Correct this.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Postgresql performance
@ 2003-10-23 01:46 CHEWTC@ap.nec.com.sg
0 siblings, 0 replies; 16+ messages in thread
From: CHEWTC@ap.nec.com.sg @ 2003-10-23 01:46 UTC (permalink / raw)
To: pgsql-performance
Hi
The Postgresql package came from the Redhat v9.0 CDROM.
I have checked the version using psql --version and it showed v7.3.2
How to check the pg_dump version?
Thank you,
REgards.
Josh Berkus
<josh@agliodbs.co To: CHEWTC@ap.nec.com.sg, pgsql-performance@postgresql.org
m> cc:
Subject: Re: [PERFORM] Postgresql performance
23/10/2003 12:06
AM
NEC,
> After a few weeks of usage, when we do a \d at the sql prompt, there was
a
> duplicate object name, ie it can be a duplicate row of index or table.
> When we do a \d table_name, it will show a duplication of column names
> inside the table.
I think the version of PSQL and pg_dump which you are using do not match
the
back-end database version. Correct this.
--
Josh Berkus
Aglio Database Solutions
San Francisco
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: Postgresql performance
@ 2003-10-23 01:47 CHEWTC@ap.nec.com.sg
0 siblings, 0 replies; 16+ messages in thread
From: CHEWTC@ap.nec.com.sg @ 2003-10-23 01:47 UTC (permalink / raw)
To: pgsql-performance
Hi
The Postgresql package came from the Redhat v9.0 CDROM.
I have checked the version using psql --version and it showed v7.3.2
The duplication of table names is in the same schema.
How to check the pg_dump version?
Thank you,
REgards.
Tom Lane
<tgl@sss.pgh.pa.u To: CHEWTC@ap.nec.com.sg
s> cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] Postgresql performance
22/10/2003 10:03
PM
CHEWTC@ap.nec.com.sg writes:
> Currently we are running Postgresql v7.3.2 on Redhat Linux OS v9.0. We
have
> Windows2000 client machines inserting records into the Postgresql tables
> via ODBC.
> After a few weeks of usage, when we do a \d at the sql prompt, there was
a
> duplicate object name, ie it can be a duplicate row of index or table.
> When we do a \d table_name, it will show a duplication of column names
> inside the table.
Are you sure you are using 7.3 psql? This sounds like something that
could happen with a pre-7.3 (not schema aware) psql, if there are
multiple occurrences of the same table name in different schemas.
> It doesnt affect the insertion/updating of the tables, but when we do a
> pg_dump -Da -t <table_name> <db_name> > /exp/<table_name>.sql, it will
not
> do a proper backup/dump.
I'd wonder about whether you have the right pg_dump, too.
regards, tom lane
^ permalink raw reply [nested|flat] 16+ messages in thread
* postgresql performance
@ 2008-03-05 10:27 SPMLINGAM <spmlingam@gmail.com>
2008-03-05 10:39 ` Re: postgresql performance Steinar H. Gunderson <sgunderson@bigfoot.com>
2008-03-05 14:09 ` Re: postgresql performance Dave Dutcher <dave@tridecap.com>
0 siblings, 2 replies; 16+ messages in thread
From: SPMLINGAM @ 2008-03-05 10:27 UTC (permalink / raw)
To: pgsql-performance
Dear Friends,
I have a table with 50 lakhs records, the table has more then 10
fields, i have primary key, i have select query with count(*) without any
condition, it takes 17 seconds.
I have another one query which will do joins with other small tables, it
takes 47 seconds to give output, the result has 2 lakhs records. the
indexing is not used. I have created one index with one field ( which i
used in this query, the field value has duplicates also ).
Now the cpu usage is 4.4%, and waiting for io process .
i have checked the above in the top command.
If i delete that index, then it works fine, now the cpu usage takes more
then 70%, and waiting shows less then 30%.
Can Anyone explain, Why postgres behaves like this.
--
View this message in context: http://www.nabble.com/postgresql-performance-tp15847165p15847165.html
Sent from the PostgreSQL - performance mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: postgresql performance
2008-03-05 10:27 postgresql performance SPMLINGAM <spmlingam@gmail.com>
@ 2008-03-05 10:39 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
2008-03-05 10:52 ` Re: postgresql performance Franck Routier <franck.routier@axege.com>
2008-03-05 15:46 ` Re: postgresql performance Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 2 replies; 16+ messages in thread
From: Steinar H. Gunderson @ 2008-03-05 10:39 UTC (permalink / raw)
To: SPMLINGAM <spmlingam@gmail.com>; +Cc: pgsql-performance
On Wed, Mar 05, 2008 at 02:27:08AM -0800, SPMLINGAM wrote:
> I have a table with 50 lakhs records, the table has more then 10
> fields, i have primary key, i have select query with count(*) without any
> condition, it takes 17 seconds.
Without knowing what a "lakhs" record is, it's pretty obvious that you
haven't vacuumed in a very long time. Run VACUUM FULL on your tables, then
instate regular (non-FULL) VACUUMs or enable autovacuum.
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: postgresql performance
2008-03-05 10:27 postgresql performance SPMLINGAM <spmlingam@gmail.com>
2008-03-05 10:39 ` Re: postgresql performance Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2008-03-05 10:52 ` Franck Routier <franck.routier@axege.com>
2008-03-05 11:03 ` Re: postgresql performance Claus Guttesen <kometen@gmail.com>
1 sibling, 1 reply; 16+ messages in thread
From: Franck Routier @ 2008-03-05 10:52 UTC (permalink / raw)
To: pgsql-performance
Hi,
Le mercredi 05 mars 2008 à 11:39 +0100, Steinar H. Gunderson a écrit :
> Without knowing what a "lakhs" record is,
I had the same question... and Wikipedia gave me the answer : it is an
Indian word meaning 10^5, often used in indian english.
Franck
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: postgresql performance
2008-03-05 10:27 postgresql performance SPMLINGAM <spmlingam@gmail.com>
2008-03-05 10:39 ` Re: postgresql performance Steinar H. Gunderson <sgunderson@bigfoot.com>
2008-03-05 10:52 ` Re: postgresql performance Franck Routier <franck.routier@axege.com>
@ 2008-03-05 11:03 ` Claus Guttesen <kometen@gmail.com>
0 siblings, 0 replies; 16+ messages in thread
From: Claus Guttesen @ 2008-03-05 11:03 UTC (permalink / raw)
To: Franck Routier <franck.routier@axege.com>; +Cc: pgsql-performance
> > Without knowing what a "lakhs" record is,
>
> I had the same question... and Wikipedia gave me the answer : it is an
> Indian word meaning 10^5, often used in indian english.
Thank you (both OP and this post) for enlightening us with this word.
--
regards
Claus
When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.
Shakespeare
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: postgresql performance
2008-03-05 10:27 postgresql performance SPMLINGAM <spmlingam@gmail.com>
2008-03-05 10:39 ` Re: postgresql performance Steinar H. Gunderson <sgunderson@bigfoot.com>
@ 2008-03-05 15:46 ` Kevin Grittner <Kevin.Grittner@wicourts.gov>
1 sibling, 0 replies; 16+ messages in thread
From: Kevin Grittner @ 2008-03-05 15:46 UTC (permalink / raw)
To: Steinar H. Gunderson <sgunderson@bigfoot.com>; SPMLINGAM <spmlingam@gmail.com>; +Cc: pgsql-performance
>>> On Wed, Mar 5, 2008 at 4:39 AM, in message <20080305103945.GA3673@uio.no>,
"Steinar H. Gunderson" <sgunderson@bigfoot.com> wrote:
> it's pretty obvious that you
> haven't vacuumed in a very long time. Run VACUUM FULL on your tables
If you use VACUUM FULL, you should probably throw in ANALYZE with
it, and REINDEX, too. An alternative that is probably faster, but
which requires that you have enough free space for a temporary
additional copy of the data, is to CLUSTER the bloated tables,
which automatically takes care of the indexes, but requires a
subsequent ANALYZE.
> regular (non-FULL) VACUUMs or enable autovacuum.
Absolutely!
-Kevin
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: postgresql performance
2008-03-05 10:27 postgresql performance SPMLINGAM <spmlingam@gmail.com>
@ 2008-03-05 14:09 ` Dave Dutcher <dave@tridecap.com>
2008-03-05 16:00 ` Re: postgresql performance Bill Moran <wmoran@collaborativefusion.com>
1 sibling, 1 reply; 16+ messages in thread
From: Dave Dutcher @ 2008-03-05 14:09 UTC (permalink / raw)
To: 'SPMLINGAM' <spmlingam@gmail.com>; pgsql-performance
> -----Original Message-----
> From: SPMLINGAM
> Subject: [PERFORM] postgresql performance
>
> Dear Friends,
> I have a table with 50 lakhs records, the table has more
> then 10 fields, i have primary key, i have select query with
> count(*) without any condition, it takes 17 seconds.
17 seconds to scan 5 million records doesn't sound that bad to me.
Postgresql does not store a count of records, and so it has to actually scan
the table to count all the records. This was a design choice because select
count(*) isn't usually used in a production system.
> I have another one query which will do joins with other
> small tables, it takes 47 seconds to give output, the result
> has 2 lakhs records. the indexing is not used. I have
> created one index with one field ( which i used in this
> query, the field value has duplicates also ).
You should post which version of Postgresql you are using, your table
definition, and the output of EXPLAIN ANALYSE run on your query. If you
have a lot of IO wait, you are most likely IO bound. When Postgresql is
using a lot of CPU it is likely performing a sort or hashing. Pulling a
large number of rows out of an even larger table can be difficult to do
extremely quickly, but if you post the EXPLAIN ANALYZE output we would know
if things could be improved or not.
Dave
^ permalink raw reply [nested|flat] 16+ messages in thread
* Re: postgresql performance
2008-03-05 10:27 postgresql performance SPMLINGAM <spmlingam@gmail.com>
2008-03-05 14:09 ` Re: postgresql performance Dave Dutcher <dave@tridecap.com>
@ 2008-03-05 16:00 ` Bill Moran <wmoran@collaborativefusion.com>
0 siblings, 0 replies; 16+ messages in thread
From: Bill Moran @ 2008-03-05 16:00 UTC (permalink / raw)
To: Dave Dutcher <dave@tridecap.com>; +Cc: 'SPMLINGAM' <spmlingam@gmail.com>; pgsql-performance
In response to "Dave Dutcher" <dave@tridecap.com>:
> > -----Original Message-----
> > From: SPMLINGAM
> > Subject: [PERFORM] postgresql performance
> >
> > Dear Friends,
> > I have a table with 50 lakhs records, the table has more
> > then 10 fields, i have primary key, i have select query with
> > count(*) without any condition, it takes 17 seconds.
>
> 17 seconds to scan 5 million records doesn't sound that bad to me.
> Postgresql does not store a count of records, and so it has to actually scan
> the table to count all the records. This was a design choice because select
> count(*) isn't usually used in a production system.
Note that if you need a fast count of the number of rows in a large
table, there are known workarounds to get it. Such as creating triggers
that update a count column, or using explain to get a quick estimate of
the number of rows (if that's acceptable).
--
Bill Moran
Collaborative Fusion Inc.
http://people.collaborativefusion.com/~wmoran/
wmoran@collaborativefusion.com
Phone: 412-422-3463x4023
****************************************************************
IMPORTANT: This message contains confidential information and is
intended only for the individual named. If the reader of this
message is not an intended recipient (or the individual
responsible for the delivery of this message to an intended
recipient), please be advised that any re-use, dissemination,
distribution or copying of this message is prohibited. Please
notify the sender immediately by e-mail if you have received
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or
error-free as information could be intercepted, corrupted, lost,
destroyed, arrive late or incomplete, or contain viruses. The
sender therefore does not accept liability for any errors or
omissions in the contents of this message, which arise as a
result of e-mail transmission.
****************************************************************
^ permalink raw reply [nested|flat] 16+ messages in thread
end of thread, other threads:[~2008-03-05 16:00 UTC | newest]
Thread overview: 16+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-12 20:47 Postgresql performance Enix Empreendimentos e Construções Ltda. <contato@enix.com.br>
2003-03-12 21:38 ` Chad Thompson <chad@weblinkservices.com>
2003-03-12 21:45 ` Rod Taylor <rbt@rbt.ca>
2003-03-13 22:26 ` Hannu Krosing <hannu@tm.ee>
2003-10-22 10:25 Postgresql performance CHEWTC@ap.nec.com.sg
2003-10-22 14:03 ` Re: Postgresql performance Tom Lane <tgl@sss.pgh.pa.us>
2003-10-22 16:06 ` Re: Postgresql performance Josh Berkus <josh@agliodbs.com>
2003-10-23 01:46 Re: Postgresql performance CHEWTC@ap.nec.com.sg
2003-10-23 01:47 Re: Postgresql performance CHEWTC@ap.nec.com.sg
2008-03-05 10:27 postgresql performance SPMLINGAM <spmlingam@gmail.com>
2008-03-05 10:39 ` Re: postgresql performance Steinar H. Gunderson <sgunderson@bigfoot.com>
2008-03-05 10:52 ` Re: postgresql performance Franck Routier <franck.routier@axege.com>
2008-03-05 11:03 ` Re: postgresql performance Claus Guttesen <kometen@gmail.com>
2008-03-05 15:46 ` Re: postgresql performance Kevin Grittner <Kevin.Grittner@wicourts.gov>
2008-03-05 14:09 ` Re: postgresql performance Dave Dutcher <dave@tridecap.com>
2008-03-05 16:00 ` Re: postgresql performance Bill Moran <wmoran@collaborativefusion.com>
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