Query optimization problem

From: Peter <peter(at)greatnowhere(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Query optimization problem
Date: 2007-02-02 13:10:10
Message-ID: 45c337aa$0$1345$834e42db@reader.greatnowhere.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I'm not sure if I'm heading up the right alley - seems too simple!, but
here is my issue.

I have about 3000 records in 'mytable', and simple

select * from mytable where x=1

is timed as:

Total query runtime: 2933 ms.
Data retrieval runtime: 791 ms.

EXPLAIN says it's Seq Scan, but the actual filter expression I'm using
returns me all rows from the table anyway. If I run

select * from mytable

Total query runtime: 3444 ms.
Data retrieval runtime: 771 ms.

At the same time:

select * into x from prl_user_entities

Query returned successfully with no result in 600 ms.

Why SELECT takes 3+ second to execute? Is it something to do with my
Postgres server optimization, or PgAdmin does not show correct data
retrieval runtime (leaks over into query runtime or something)?

This is PG 8.1 on FreeBSD, server is fairly powerful PC.

Peter

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2007-02-02 13:21:01 Re: Backup ?
Previous Message Tomi N/A 2007-02-02 12:33:08 Re: database backup trouble