Re: querying while copying into a table and optimizations

From: David Eduardo Gómez Noguera <davidgn(at)servidor(dot)unam(dot)mx>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: querying while copying into a table and optimizations
Date: 2005-10-30 22:44:55
Message-ID: 1130712295.10539.1.camel@mokona
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

On Sat, 2005-10-29 at 09:10 -0600, Michael Fuhr wrote:
> On Fri, Oct 28, 2005 at 06:22:43PM -0000, davidgn(at)servidor(dot)unam(dot)mx wrote:
> > This is postgresql 7.4
> > I am trying to check that postgres is updating a table.
> > I have a pretty large ascii table file (+- 210 Mb) which I am copying into a
> > table with pgsql, but it is taking a long time, and any select query I do to the
> > table returns me 0 rows
>
> Presumably you're using COPY or doing multiple inserts inside a
> transaction (hopefully the former for performance reasons). No
> other transaction will be able to see any of the data until the
> inserting transaction commits it (a single statement like COPY is
> wrapped in its own transaction even if it occurs outside an explicit
> transaction block). You could use contrib/pgstattuple to check on
> the copy/insert's progress, but that won't allow you to query the
> data itself.
>
Yep. The script uses COPY to populate the database right after erasing
all its contents.
I was wondering about querying just as a mean of feedback, as you
guessed, but anything else would be right.
I initially though that read access was available as each row was
copied.

I have been playing with this script, which was handed to me, and before
DELETE and COPY they run a vacuumdb on the whole database.

I commented that line out and the COPY ran in about 5.5 mins (under
postgres 7.3 the script runs all right)
I am wondering if that was what was somehow causing the problem, as I
did some lot of stuff with the database and couldn't be sure.

I found some discussions about ANALYZE, but such discussions were under
Postgres 8, so I don't know if this is related.

> > I am a bit clueless as to what can I do to the configuration files to optimize
> > this copy.
>
> See "Populating a Database" in the "Performance Tips" chapter of
> the documentation for some ideas.
>
> http://www.postgresql.org/docs/8.0/interactive/populate.html
>

Thank you.
A lot of stuff I can't grab yet. Guess I need some more experience
administering databases.

Sorry about the double mail, I didn't realize I hadn't replied to the
list. Lest this be archived.
--
David Eduardo Gómez Noguera <davidgn(at)servidor(dot)unam(dot)mx>

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Vlad 2005-10-31 03:30:45 Starting PostgreSQL 8.0.4 with more memory [FreeBSD 6.0]
Previous Message Florian G. Pflug 2005-10-30 22:31:25 Re: Oracle 10g Express - any danger for Postgres?