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

> 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?

I'm running my script from VFP client applicaton.
Application sends every statement to server separately using ODBC driver.

table creation, data loading, primary key creation, index creation, analyze
and problematic CREATE TABLE TEMP command
all ran in single transaction.
Should I commit transactions after analyze command or after index creation?

server logs shows:

2007-06-13 03:19:43 LOG:  checkpoints are occurring too frequently (21
seconds apart)
2007-06-13 03:19:43 HINT:  Consider increasing the configuration parameter
"checkpoint_segments".
2007-06-13 03:20:02 LOG:  checkpoints are occurring too frequently (19
seconds apart)
2007-06-13 03:20:02 HINT:  Consider increasing the configuration parameter
"checkpoint_segments".
2007-06-13 03:20:22 LOG:  checkpoints are occurring too frequently (20
seconds apart)
2007-06-13 03:20:22 HINT:  Consider increasing the configuration parameter
"checkpoint_segments".
2007-06-13 03:21:18 LOG:  checkpoints are occurring too frequently (23
seconds apart)
2007-06-13 03:21:18 HINT:  Consider increasing the configuration parameter
"checkpoint_segments".
2007-06-13 03:49:10 ERROR:  deadlock detected
2007-06-13 03:49:10 DETAIL:  Process 3280 waits for AccessExclusiveLock on
relation 233893 of database 233756; blocked by process 2508.
 Process 2508 waits for ShareUpdateExclusiveLock on relation 233988 of
database 233756; blocked by process 3280.
2007-06-13 03:49:10 STATEMENT:  ALTER TABLE desktop ADD FOREIGN KEY
 (alamklass) REFERENCES andmetp ON UPDATE CASCADE      DEFERRABLE

and script terminates after 5.5 hours running yesterday night.
I will re-start computer and try again.

Can increasing checkpint_segments increase speed significantly ?

>> 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 had a number of DELETE .. WHERE NOT IN commands.
I changed all them to CREATE TEMP TABLE ...   DELETE

>> 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_database_size('mydb'))

returns

828 MB


> SELECT pg_size_pretty(pg_relation_size(' bilkaib'))

returns 100 MB

> SELECT pg_size_pretty(pg_total_relation_size(' bilkaib'))

returns "171 MB"

relpages * 8/1024 and pg_relation_size(oid) return in some cases very
different result, no idea why.

For one index relpages returns size about 6 MB  but pg_relation_size returns
only 2152 kB


>> 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?

Yes, I meant table.

>> Remaining 5 hours are used for index and key creation. This seems too
>> much.
> 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.

I created primary key on dok(dokumnr), index on rid(dokumnr) and  ran
analyze before running
this CREATE TEMP TABLE command.

> 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.

If CREATE TEMP TABLE from pgAdmin takes 1 sec and from script 11 minues I
do'nt think this is hardware related.

> If that does not solve your problem, post your setup or your script -
> whichever you suspect to be the problem

The script which creates 800 MB database  is big.
I can create this script but is anybody interested to look into it ?

Andrus.