PostgreSQL performance in simple queries

Поиск
Список
Период
Сортировка
От Eugeny Balakhonov
Тема PostgreSQL performance in simple queries
Дата
Msg-id 1117434122.20040520000757@mail.ru
обсуждение исходный текст
Ответы Re: PostgreSQL performance in simple queries
Re: PostgreSQL performance in simple queries
Список 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




В списке pgsql-performance по дате отправления:

Предыдущее
От: Joseph Shraibman
Дата:
Сообщение: shared buffer size on linux
Следующее
От: "Gary Doades"
Дата:
Сообщение: Re: PostgreSQL performance in simple queries