Thread: psql performance
How come when I paste a large query into psql it starts off fast but then slows to a crawl eating up cpu just echoing the query back to me? I'm using psql 7.4.7
What is the query? What is the schema for the tables in the query? What is the cardinality of the tables? What does the planner say, when you do this: explain <query> explain analyze <query> http://www.faqs.org/docs/ppbook/r26943.htm -----Original Message----- From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Joseph Shraibman Sent: Thursday, April 14, 2005 6:19 PM To: pgsql-general Subject: [GENERAL] psql performance How come when I paste a large query into psql it starts off fast but then slows to a crawl eating up cpu just echoing the query back to me? I'm using psql 7.4.7 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to majordomo@postgresql.org so that your message can get through to the mailing list cleanly
I am not sure if this is relevant, but I think it may depend on what kind of shell you are using. I use putty on windows XP, and it seemed to me that when I had the encoding set to Latin-1, it always pasted very fast, but when I had it set to UTF8, the paste would be slow. I concluded (perhaps wrongly--I have not tested much) that putty goes much slower when using UTF8. Have you confirmed that psql is at fault, and not your shell? (I.e., try pasting directly to the shell--hopefully your query doesn't start with rm -rf / : ) Regards, Paul Tillotson Joseph Shraibman wrote: > How come when I paste a large query into psql it starts off fast but > then slows to a crawl eating up cpu just echoing the query back to me? > I'm using psql 7.4.7 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
The cpu on the remote server is being used up by psql, and my shell is local. Paul Tillotson wrote: > I am not sure if this is relevant, but I think it may depend on what > kind of shell you are using. I use putty on windows XP, and it seemed > to me that when I had the encoding set to Latin-1, it always pasted very > fast, but when I had it set to UTF8, the paste would be slow. I > concluded (perhaps wrongly--I have not tested much) that putty goes much > slower when using UTF8. > > Have you confirmed that psql is at fault, and not your shell? (I.e., > try pasting directly to the shell--hopefully your query doesn't start > with rm -rf / : ) > > Regards, > Paul Tillotson > > Joseph Shraibman wrote: > >> How come when I paste a large query into psql it starts off fast but >> then slows to a crawl eating up cpu just echoing the query back to me? >> I'm using psql 7.4.7 >> >> ---------------------------(end of broadcast)--------------------------- >> TIP 3: if posting/reading through Usenet, please send an appropriate >> subscribe-nomail command to majordomo@postgresql.org so that your >> message can get through to the mailing list cleanly >> >> > > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly
It doesn't matter what the query is. The problem happens before it even runs the query. Just try pasting select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'; ... and see how long it takes just to get to the point where it executes the query. Incidentally when I did that I only got back one row. What's up with that? Dann Corbit wrote: > What is the query? > What is the schema for the tables in the query? > What is the cardinality of the tables? > > What does the planner say, when you do this: > explain <query> > explain analyze <query> > > http://www.faqs.org/docs/ppbook/r26943.htm > > -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Joseph > Shraibman > Sent: Thursday, April 14, 2005 6:19 PM > To: pgsql-general > Subject: [GENERAL] psql performance > > How come when I paste a large query into psql it starts off fast but > then slows to a crawl eating up cpu just echoing the query back to me? > I'm using psql 7.4.7 > > ---------------------------(end of broadcast)--------------------------- > TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > > ---------------------------(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
Joseph Shraibman wrote: > It doesn't matter what the query is. The problem happens before it even > runs the query. Just try pasting select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select I can not reproduce your problem on 8.0.2. Sincerely, Joshua D. Drake -- Your PostgreSQL solutions provider, Command Prompt, Inc. 24x7 support - 1.800.492.2240, programming, and consulting Home of PostgreSQL Replicator, plPHP, plPerlNG and pgPHPToolkit http://www.commandprompt.com / http://www.postgresql.org
Joseph Shraibman <jks@selectacast.net> writes:
> It doesn't matter what the query is. The problem happens before it even
> runs the query.
Hmmm ... I see different misbehavior (psql seems to lock up entirely,
it doesn't slow down or eat CPU). But it's still misbehavior.
"psql -n" doesn't have a problem, which says that this is probably a
readline bug rather than our own bug. I think it's probably context
dependent too; I'm pretty sure I've pasted big queries into psql without
a problem while working directly on my work machine, but right now I'm
ssh'd into it from a laptop and I do see a problem.
[ further experimentation... ] A local psql on the laptop doesn't
show a problem either. Both this and the one on the work machine
are 8.1devel using readline-4.2a, so it doesn't seem to be an issue
of different software versions. Maybe readline doesn't like ssh?
[ still further experimentation... ] No, because ssh'ing to my *other*
work machine and running psql there doesn't show a problem either.
Curiouser and curiouser. But it's clearly very context dependent.
You should probably tell us *exactly* what you are running, in every bit
of software between you and psql. My tests here are with (local) Mac OS
X 10.3.8, Apple-supplied X server and xterm terminal, Apple-supplied ssh
(seems to be 3.6.1p1), local psql is current PG sources + readline 4.2a.
The remote that doesn't work nicely is HPUX 10.20, sshd is
openssh-3.7.1p2, current psql sources, readline 4.2a. The remote that
does work nicely is Fedora Core 3, sshd 3.9p1-8.0.1, current psql
sources, readline 4.3-13. I'm not seeing a pattern ...
> Incidentally when I did that I only got back one row. What's up with that?
UNION eliminates duplicates.
regards, tom lane
On Thu, Apr 14, 2005 at 11:05:37PM -0400, Joseph Shraibman wrote: > It doesn't matter what the query is. The problem happens before it even > runs the query. This is most certainly a problem with the shell or the terminal. I have seen this effect somewhere else, but I can't reproduce it with psql in an xterm. > Incidentally when I did that I only got back one row. What's up with that? Try with "union all" instead of plain union. -- Alvaro Herrera (<alvherre[@]dcc.uchile.cl>) "Doing what he did amounts to sticking his fingers under the hood of the implementation; if he gets his fingers burnt, it's his problem." (Tom Lane)
On Thu, Apr 14, 2005 at 08:17:55PM -0700, Joshua D. Drake wrote: > Joseph Shraibman wrote: > >It doesn't matter what the query is. The problem happens before it even > >runs the query. Just try pasting select > >'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > >'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > >'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > > I can not reproduce your problem on 8.0.2. Nor I with 7.2.7, 7.3.9, 7.4.7, 8.0.2, or 8.1devel. What happens if you edit the query with \e and then issue it, or if you put the query in a file and use \i to read it? Could you tell us something about your environment -- OS, window manager, terminal, etc.? > >Incidentally when I did that I only got back one row. What's up with that? That's how UNION works: http://www.postgresql.org/docs/7.4/interactive/queries-union.html "Furthermore, it eliminates all duplicate rows, in the sense of DISTINCT, unless UNION ALL is used." -- Michael Fuhr http://www.fuhr.org/~mfuhr/
Don't see a problem pasting this one. Neither to a local nor to a remote ssh (running psql certainly). This is 7.4.7 on redhat and mandrake linux'es I'd suspect it has nothing to do with psql. Can you paste that into a normal ssh / terminal ? On Thursday 14 April 2005 20:05, Joseph Shraibman wrote: > It doesn't matter what the query is. The problem happens before it even > runs the query. Just try pasting select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' union select > 'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'; > > ... and see how long it takes just to get to the point where it executes > the query. > > Incidentally when I did that I only got back one row. What's up with that? > > Dann Corbit wrote: > > What is the query? > > What is the schema for the tables in the query? > > What is the cardinality of the tables? > > > > What does the planner say, when you do this: > > explain <query> > > explain analyze <query> > > > > http://www.faqs.org/docs/ppbook/r26943.htm > > > > -----Original Message----- > > From: pgsql-general-owner@postgresql.org > > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Joseph > > Shraibman > > Sent: Thursday, April 14, 2005 6:19 PM > > To: pgsql-general > > Subject: [GENERAL] psql performance > > > > How come when I paste a large query into psql it starts off fast but > > then slows to a crawl eating up cpu just echoing the query back to me? > > I'm using psql 7.4.7 > > > > ---------------------------(end of broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please send an appropriate > > subscribe-nomail command to majordomo@postgresql.org so that your > > message can get through to the mailing list cleanly > > > > ---------------------------(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 > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://archives.postgresql.org -- UC -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax: +1 650 872 2417
Alvaro Herrera wrote: >>Incidentally when I did that I only got back one row. What's up with that? > > > Try with "union all" instead of plain union. > Talk about serendipity. The problem I've been struggling with for the last few hours has been why my query wasn't producing sorted output even though I put in an ORDER BY and the EXPLAIN shows that it is ordering. The DISTINCT implied by the UNION must have been messing up the sorting. The docs say (http://www.postgresql.org/docs/7.4/interactive/sql-select.html#SQL-UNION): select_statement is any SELECT statement without an ORDER BY, LIMIT, or FOR UPDATE clause. (ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. ... but I *did* put my SELECTs in parentheses. This is either a bug in pg or a serious ommision from the docs.
I'm sshing into my redhat 8 server and running psql 7.4.7. My client is fedora core 3. With konsole terminals I have the problem but with xterm I don't. I did a diff between the output of env of both of them and got: -SSH_CLIENT=66.xxx.xxx.124 32943 22 -SSH_TTY=/dev/pts/5 +SSH_CLIENT=66.xxx.xxx.124 33110 22 +SSH_TTY=/dev/pts/6 (I sanatized my ip) So the question what is the difference between konsole and xterm that is causing cpu to be eating up on the server? My ssh client: [jks@jks-desktop ~]$ ssh -V OpenSSH_3.9p1, OpenSSL 0.9.7a Feb 19 2003 server sshd: sshd version OpenSSH_3.4p1 server:> rpm -qa | grep readline readline-devel-4.3-3 readline-4.3-3 [jks@jks-desktop ~]$ rpm -qa | grep readline readline-4.3-13 readline-devel-4.3-13 Tom Lane wrote: > Joseph Shraibman <jks@selectacast.net> writes: > >>It doesn't matter what the query is. The problem happens before it even >>runs the query. > > > Hmmm ... I see different misbehavior (psql seems to lock up entirely, > it doesn't slow down or eat CPU). But it's still misbehavior. > > "psql -n" doesn't have a problem, which says that this is probably a > readline bug rather than our own bug. I think it's probably context > dependent too; I'm pretty sure I've pasted big queries into psql without > a problem while working directly on my work machine, but right now I'm > ssh'd into it from a laptop and I do see a problem. > > [ further experimentation... ] A local psql on the laptop doesn't > show a problem either. Both this and the one on the work machine > are 8.1devel using readline-4.2a, so it doesn't seem to be an issue > of different software versions. Maybe readline doesn't like ssh? > > [ still further experimentation... ] No, because ssh'ing to my *other* > work machine and running psql there doesn't show a problem either. > Curiouser and curiouser. But it's clearly very context dependent. > > You should probably tell us *exactly* what you are running, in every bit > of software between you and psql. My tests here are with (local) Mac OS > X 10.3.8, Apple-supplied X server and xterm terminal, Apple-supplied ssh > (seems to be 3.6.1p1), local psql is current PG sources + readline 4.2a. > The remote that doesn't work nicely is HPUX 10.20, sshd is > openssh-3.7.1p2, current psql sources, readline 4.2a. The remote that > does work nicely is Fedora Core 3, sshd 3.9p1-8.0.1, current psql > sources, readline 4.3-13. I'm not seeing a pattern ... > > >>Incidentally when I did that I only got back one row. What's up with that? > > > UNION eliminates duplicates. > > regards, tom lane > > ---------------------------(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
Joseph Shraibman wrote: > > So the question what is the difference between konsole and xterm that is > causing cpu to be eating up on the server? > Scratch that. I wasn't using the same input for both queries. Both of them are slow. I discovered that adding newlines to the query speeds things up a lot.
Uwe C. Schroeder wrote: > Don't see a problem pasting this one. > Neither to a local nor to a remote ssh (running psql certainly). > This is 7.4.7 on redhat and mandrake linux'es > > I'd suspect it has nothing to do with psql. Can you paste that into a normal > ssh / terminal ? > It is slow just pasting to the shell prompt too. But when I paste it into psql I can see psql's cpu shoot to 100%, that's why I originally through it was a psql bug. I tried this on redhat 9 servers also, same problem. readline-4.3-5 readline-devel-4.3-5 sshd version OpenSSH_3.5p1
On Thu, Apr 14, 2005 at 11:05:37PM -0400, Joseph Shraibman wrote: > Incidentally when I did that I only got back one row. What's up with that? That's PostgreSQL acting according to ANSI SQL. If you want multiple rows, you need UNION ALL. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"
On Fri, Apr 15, 2005 at 12:23:14AM -0400, Joseph Shraibman wrote: > > > Alvaro Herrera wrote: > > >>Incidentally when I did that I only got back one row. What's up with > >>that? > > > > > >Try with "union all" instead of plain union. > > > Talk about serendipity. The problem I've been struggling with for the > last few hours has been why my query wasn't producing sorted output even > though I put in an ORDER BY and the EXPLAIN shows that it is ordering. > The DISTINCT implied by the UNION must have been messing up the sorting. > > The docs say > (http://www.postgresql.org/docs/7.4/interactive/sql-select.html#SQL-UNION): > > select_statement is any SELECT statement without an ORDER BY, LIMIT, or > FOR UPDATE clause. (ORDER BY and LIMIT can be attached to a > subexpression if it is enclosed in parentheses. > > > > ... but I *did* put my SELECTs in parentheses. This is either a bug in > pg or a serious ommision from the docs. It's not a bug, though it could possibly made clearer in the docs. If you want your final output in a specific order, you have to put you ORDER BY in the very outermost level of the query. PostgreSQL is pretty liberal about where you can put ORDER BY, but when it comes to output ordering only the order of the final query step matters. Why allow ordering elsewhere? Consider this more-performant replacement for SELECT max(blah): SELECT max FROM (SELECT blah FROM table ORDER BY blah DESC LIMIT 1) a ; Maybe not a great example since you'll only get one row back, but the point is that the ORDER BY in the subquery doesn't mean a thing when it comes to output order. -- Jim C. Nasby, Database Consultant decibel@decibel.org Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?"