PostgreSQL performance in simple queries

From: Eugeny Balakhonov <c0ff75(at)mail(dot)ru>
To: pgsql-performance(at)postgresql(dot)org
Subject: PostgreSQL performance in simple queries
Date: 2004-05-19 20:07:57
Message-ID: 1117434122.20040520000757@mail.ru
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers pgsql-performance

Hello for all!

I have PostgreSQL 7.4 under last version of Cygwin and have some
problems with performance :( It is very strange... I don't remember
this problem on previous version Cygwin and PostgreSQL 7.3

I have only two simple tables:

CREATE TABLE public.files_t
(
id int8 NOT NULL,
parent int8,
size int8 NOT NULL,
dir bool NOT NULL DEFAULT false,
ctime timestamp NOT NULL,
ftime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone,
name text NOT NULL,
access varchar(10) NOT NULL,
host int4 NOT NULL,
uname text NOT NULL,
CONSTRAINT pk_files_k PRIMARY KEY (id),
CONSTRAINT fk_files_k FOREIGN KEY (parent) REFERENCES public.files_t (id) ON UPDATE CASCADE ON DELETE CASCADE,
CONSTRAINT fk_hosts_k FOREIGN KEY (host) REFERENCES public.hosts_t (id) ON UPDATE CASCADE ON DELETE CASCADE
) WITH OIDS;

and

CREATE TABLE public.hosts_t
(
id int4 NOT NULL,
ftime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone,
utime timestamp NOT NULL DEFAULT ('now'::text)::timestamp(6) with time zone,
name text NOT NULL,
address inet NOT NULL,
CONSTRAINT pk_hosts_k PRIMARY KEY (id)
) WITH OIDS;

Table files_t has 249259 records and table hosts_t has only 59 records.

I tries to run simple query:

select * from files_t where parent = 3333

This query works 0.256 seconds! It is very big time for this small
table!
I have index for field "parent":

CREATE INDEX files_parent_idx
ON public.files_t
USING btree
(parent);

But if I tries to see query plan then I see following text:

Seq Scan on files_t (cost=0.00..6103.89 rows=54 width=102)
Filter: (parent = 3333)

PostgreSQL do not uses index files_parent_idx!

I have enabled all options of "QUERY TUNING" in postgresql.conf, I
have increased memory sizes for PostgreSQL:

shared_buffers = 2000 # min 16, at least max_connections*2, 8KB each
sort_mem = 32768 # min 64, size in KB
vacuum_mem = 65536 # min 1024, size in KB
fsync = false # turns forced synchronization on or off
checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
enable_hashagg = true
enable_hashjoin = true
enable_indexscan = true
enable_mergejoin = true
enable_nestloop = true
enable_seqscan = true
enable_sort = true
enable_tidscan = true
geqo = true
geqo_threshold = 22
geqo_effort = 1
geqo_generations = 0
geqo_pool_size = 0 # default based on tables in statement,
# range 128-1024
geqo_selection_bias = 2.0 # range 1.5-2.0
stats_start_collector = true
stats_command_string = true
stats_block_level = true
stats_row_level = true
stats_reset_on_server_start = false

Please help me!
My database has a very small size (only 249259 records) but it works
very slowly :(

Best regards
Eugeny

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2004-05-19 20:10:05 Re: proposal: be smarter about i/o patterns in index scan
Previous Message Jeffrey W. Baker 2004-05-19 20:07:40 Re: proposal: be smarter about i/o patterns in index scan

Browse pgsql-performance by date

  From Date Subject
Next Message Gary Doades 2004-05-19 20:23:47 Re: PostgreSQL performance in simple queries
Previous Message Joseph Shraibman 2004-05-19 19:26:31 shared buffer size on linux