Re: PostgreSQL performance in simple queries

Try using

select * from files_t where parent = 3333::int8

You have declared parent as int8, but the query will assume int4 for "3333" and may not
use the index.

Also make sure you have ANALYZEd this table.

Regards,
Gary.

On 20 May 2004 at 0:07, Eugeny Balakhonov wrote:

> 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
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo(at)postgresql(dot)org so that your
> message can get through to the mailing list cleanly