Thread: \d very, very, very slow

\d very, very, very slow

From
Steve Atkins
Date:
I'm seeing very, very slow response to \d <tablename> - around 15-20
seconds on an otherwise idle Netra.

PostgreSQL 7.2.1 on sparc-sun-solaris2.8, compiled by cc -xarch=v9 -Xa

Running psql -E shows that \d is issuing 4 queries. Picking one at
random at running it through explain analyze shows three nested
seqscans and a few joins, taking 6500ms.

(Running the same query on a Linux box running Postgresql 7.2 with the
 same schema and similar postgresql.conf  shows the same query plan,
 taking 80ms.)

So is 15-20 seconds for \d unusual? If so, where should I start looking
to see what's broken?

Any thoughts appreciated.

Cheers,
  Steve



Re: \d very, very, very slow

From
Tom Lane
Date:
Steve Atkins <steve@blighty.com> writes:
> So is 15-20 seconds for \d unusual?

Very.

> If so, where should I start looking to see what's broken?

Well, what query plans are used for the queries issued by \d on each
of your two boxes?  Have you done ANALYZE or VACUUM ANALYZE lately?
(Out-of-date stats about the system tables might lead the planner to
make a stupid choice of query plan.)

            regards, tom lane

Re: \d very, very, very slow

From
Steve Atkins
Date:
On Sun, Aug 04, 2002 at 01:51:55AM -0400, Tom Lane wrote:
> Steve Atkins <steve@blighty.com> writes:
> > So is 15-20 seconds for \d unusual?
>
> Very.
>
> > If so, where should I start looking to see what's broken?
>
> Well, what query plans are used for the queries issued by \d on each
> of your two boxes?  Have you done ANALYZE or VACUUM ANALYZE lately?
> (Out-of-date stats about the system tables might lead the planner to
> make a stupid choice of query plan.)

That was the problem. I'd been happily running analyze - but not as
the postgres user, so it was looking only at the user tables, not the
system tables.

The query times went from 6.5s to 1.2ms. Noticably better...

Thanks. Y'know, the help and support I've seen from Tom Lane on this
list alone exceeds everything I've ever seen from Oracle.

I'm using postgres for a long-running application[1], where I'd really
rather the application daemons don't have any access as the postgres
user. As the schema doesn't change is it safe to analyze the system
tables as the postgres user once at installation time or should I
be doing that as part of the normal cronjobbed maintenance?

Cheers,
  Steve

[1] Commercial, with pretty high data rates. I'm describing it as "based
    on the industry standard PostgreSQL database" to customers and'll be
    adding it to the list of PostrgreSQL apps once the press releases
    go out.

Re: \d very, very, very slow

From
Tom Lane
Date:
Steve Atkins <steve@blighty.com> writes:
> I'm using postgres for a long-running application[1], where I'd really
> rather the application daemons don't have any access as the postgres
> user. As the schema doesn't change is it safe to analyze the system
> tables as the postgres user once at installation time or should I
> be doing that as part of the normal cronjobbed maintenance?

If you're not doing any schema changes then the ANALYZE stats for the
system tables shouldn't need updates, probably.  On the other hand,
I'm not sure what it buys you to avoid it.  Running "vacuum analyze"
as the postgres user from a cron job is standard practice in my book.

            regards, tom lane

Re: \d very, very, very slow

From
"Sam Liddicott"
Date:

> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> Sent: 04 August 2002 06:52
> To: Steve Atkins
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] \d very, very, very slow
>
>
> Steve Atkins <steve@blighty.com> writes:
> > So is 15-20 seconds for \d unusual?
>
> Very.
>
> > If so, where should I start looking to see what's broken?
>
> Well, what query plans are used for the queries issued by \d on each
> of your two boxes?  Have you done ANALYZE or VACUUM ANALYZE lately?
> (Out-of-date stats about the system tables might lead the planner to
> make a stupid choice of query plan.)

We find that sometimes pgadmin takes this long to show us DB details when we
double click on a DB and we vacuum analyse every night.

This ispostgres 7.2.1

Sam




Installing postgresql

From
"jeff"
Date:
Howdy,

I would like to evaluate postgresql. I want to install the RPM version
on a redhat 7.1 server. I looked at the files at
http://planetmirror.com/pub/postgresql/binary/v7.2.1/RPMS/ but I can't
tell which files I need. And if they are for an intel linux why do they
have the name sparc appended at the end?


Thanks for your help,

Jeff



Re: Installing postgresql

From
Jeff Davis
Date:
I'd like to direct you to rpmfind.net, here's a link directly to postgres:

http://rpmfind.net//linux/RPM/PLD/dists/ra/PLD/i386/PLD/RPMS/postgresql-7.2.1-9.i386.html

I am not much up to date on RPMs any more (switched to debian a while back).
So the only advice I can give you is install that package, and if RPM
complains about missing packages, download the missing ones and install those
too. After it's all set up, use the command-line utilities like createdb and
psql.

Have fun!

Regards,
    Jeff Davis

On Monday 05 August 2002 01:07 am, jeff wrote:
> Howdy,
>
> I would like to evaluate postgresql. I want to install the RPM version
> on a redhat 7.1 server. I looked at the files at
> http://planetmirror.com/pub/postgresql/binary/v7.2.1/RPMS/ but I can't
> tell which files I need. And if they are for an intel linux why do they
> have the name sparc appended at the end?
>
>
> Thanks for your help,
>
> Jeff
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org


Re: Installing postgresql

From
Lamar Owen
Date:
On Monday 05 August 2002 04:07 am, jeff wrote:
> I would like to evaluate postgresql. I want to install the RPM version
> on a redhat 7.1 server. I looked at the files at
> http://planetmirror.com/pub/postgresql/binary/v7.2.1/RPMS/ but I can't
> tell which files I need.

You will have to rebuild from the source RPM for Red Hat 7.1 for Intel.  Grab
the .src.rpm out of SRPMs.  You will then need to 'rpm --rebuild' (as root)
that src.rpm.  If you get errors and it won't build, you need to make sure
that you have a sufficient development system installed -- IIRC, with Red Hat
7.1 you will need to manually install the python-devel package.  You must
have X development installed as well.

You really need to read the README.rpm-dist file -- you can see it my doing
'rpm -i postgresql-7.2.1-1PGDG.src.rpm' and navigating to
/usr/src/redhat/SOURCES.

> And if they are for an intel linux why do they
> have the name sparc appended at the end?

Because they are for a _SPARC_ Linux, not Intel.  The only machines I still
have running anything older than Red Hat 7.3 is a pair of SPARC's running Red
Hat 6.2, and since I needed to test the buildability of the source RPM on a
Red Hat 6.2 machine of any flavor, I built the SPARC set.

But whatever you do don't try to install the RPM that came with Red Hat 7.3
(which is what you will find off of rpmfind.net), unless you want to do an
equivalent of upgrading to Red Hat 7.3 ahead of time.

Although the frequency of this request has gotten me to thinking about new
directions in my RPM maintenance.  Red Hat is doing a great job of keeping
their latest distribution populated with the latest version of PostgreSQL,
but older dists are getting left out.  I will investigate what it will take
to support older versions in terms of disk space, and will see what I can do
to alleviate the situation.  I will have to acquire some more hard disks,
however.  If anyone wants to donate a 4-5 GB IDE hard drive to the cause (or
a similar-sized SCSI drive, wide, narrow, or ultra) I have a machine to build
it upon.
--
Lamar Owen
WGCR Internet Radio
1 Peter 4:11

Re: Installing postgresql

From
"Rob Hoffman"
Date:
"Lamar Owen" <lamar.owen@wgcr.org> wrote in message
news:200208051058.53834.lamar.owen@wgcr.org...
> On Monday 05 August 2002 04:07 am, jeff wrote:
> > I would like to evaluate postgresql. I want to install the RPM version
> > on a redhat 7.1 server. I looked at the files at
> > http://planetmirror.com/pub/postgresql/binary/v7.2.1/RPMS/ but I can't
> > tell which files I need.
>
> You will have to rebuild from the source RPM for Red Hat 7.1 for Intel.
Grab
> the .src.rpm out of SRPMs.  You will then need to 'rpm --rebuild' (as
root)
> that src.rpm.  If you get errors and it won't build, you need to make sure
> that you have a sufficient development system installed -- IIRC, with Red
Hat
> 7.1 you will need to manually install the python-devel package.  You must
> have X development installed as well.
>
> You really need to read the README.rpm-dist file -- you can see it my
doing
> 'rpm -i postgresql-7.2.1-1PGDG.src.rpm' and navigating to
> /usr/src/redhat/SOURCES.
>
> > And if they are for an intel linux why do they
> > have the name sparc appended at the end?
>
> Because they are for a _SPARC_ Linux, not Intel.  The only machines I
still
> have running anything older than Red Hat 7.3 is a pair of SPARC's running
Red
> Hat 6.2, and since I needed to test the buildability of the source RPM on
a
> Red Hat 6.2 machine of any flavor, I built the SPARC set.
>
> But whatever you do don't try to install the RPM that came with Red Hat
7.3
> (which is what you will find off of rpmfind.net), unless you want to do an
> equivalent of upgrading to Red Hat 7.3 ahead of time.
>
> Although the frequency of this request has gotten me to thinking about new
> directions in my RPM maintenance.  Red Hat is doing a great job of keeping
> their latest distribution populated with the latest version of PostgreSQL,
> but older dists are getting left out.  I will investigate what it will
take
> to support older versions in terms of disk space, and will see what I can
do
> to alleviate the situation.  I will have to acquire some more hard disks,
> however.  If anyone wants to donate a 4-5 GB IDE hard drive to the cause
(or
> a similar-sized SCSI drive, wide, narrow, or ultra) I have a machine to
build
> it upon.
> --
> Lamar Owen
> WGCR Internet Radio
> 1 Peter 4:11
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org

Would it be possible to put build instructions on the PostgreSQL web site
for those of us with older releases?  My first experience with rebuilding
sources with with my upgrade to 7.2 on RedHat 7.1 and while it wasn't
difficult, finding a simple set of instructions was.

Rob