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