Re: how to speed up query - Mailing list pgsql-general

Hi Andrus!

On Jun 12, 6:38 pm, "Andrus" <kobrule...@hot.ee> wrote:
> 1 second if for repeated runs from pgAdmin.
> I my script same CREATE TEMP TABLE command takes appox 11 minutes for same
> data (see log below).

I cannot make much sense of this information. I can see no reason why
your script should take 11 minutes, while executing it from pgAdmin
would take only a second. How do you run the script?


> After your suggested change my database creation script runs 6 hours.

Is that down from the 14 hours you mentioned before? Which would be an
amazing 8 hours faster?


(...)
> I used query
>
> SELECT relname as Table_Name, relpages * 8/1024 as size_in_mb
>  FROM pg_class
>  where  relpages * 8/1024>0
>  ORDER BY relpages DESC

Looks like a useful query. Compare with:
SELECT pg_size_pretty(pg_database_size(' bilkaib'))
SELECT pg_size_pretty(pg_relation_size(' bilkaib'))
SELECT pg_size_pretty(pg_total_relation_size(' bilkaib'))
See  http://www.postgresql.org/docs/8.2/interactive/functions-admin.html

> Biggest database (bilkaib) load time is 8 minutes, it contains 329000
> records.
> Total data loading time is approx 49 minutes.

You mean table, not database?


> Remaining 5 hours are used for index and key creation. This seems too much.
>
> Here is log file for minutes  49 .. 135 ie. first 86 minutes after loading
> data.
>
> It shows statements which ran more than 1 minute.
>
> First number (49,4500) is the number minutes from start of script (starting
> from database creation).
>
> The slowest statement is
>
> CREATE TEMP TABLE mydel AS
>  SELECT r.dokumnr
>  FROM rid r
>  LEFT JOIN dok d USING (dokumnr)
>  WHERE d.dokumnr IS NULL

It might be worth checking the order in which you create objects.
Creating relevant indices before using complex queries is one thing to
look for.
If that still runs so slow it's probably indication that your RDBMS is
in dire need of more RAM. Look to your setup in postgresql.conf.

As everything runs slow, you should look to your hardware, system
configuration and PostgreSQL setup. Do you have enough RAM (you
mentioned 2 GB) and does PostgreSQL get its share? (-> setup in
postgresql.conf). There is probably a bottleneck somewhere.

I have a machine just like the one you described above ( dual AMD
Opteron 240, 2 GB RAM, 10k rpm HDDs in RAID 1) - slow CPUs and
conservative RAID setup - and a complete dump AND restore of a
database where SELECT pg_size_pretty(pg_database_size('event')) = 300
MB takes about 1,5 minutes. And this is with pg 8.1.8, so your setup
should have better hardware (guessing here) and newer software. But
your DB is also a lot bigger.

Anyway, I am not a postgres hacker, I am just a DB admin myself, so
don't expect too much from me. Someone else might know more. But if
you want help, you'd better learn how to present a problem in a way,
that deserves help.


My best guess: buy at least 2 GB more RAM. Look to your settings in
postgresql.conf. Read up here:
http://www.postgresql.org/docs/current/static/performance-tips.html
http://revsys.com/writings/postgresql-performance.html
http://www.powerpostgresql.com/Docs
http://www.powerpostgresql.com/Downloads/annotated_conf_80.html

If that does not solve your problem, post your setup or your script -
whichever you suspect to be the problem - and try to present all the
necessary information in a concise manner. That is much more likely to
get help. Nobody wants to waste time, especially not helping someone
free of charge. Your first posting was just not good enough. If you
keep mixing things up, people will be frustrated and rather not help.
Read your posting, before you send it.


Regards
Erwin