Home > mailing lists

Query performance issue with 8.0.0beta1 - Mailing list pgsql-performance

From	Stefano Bonnin
Subject	Query performance issue with 8.0.0beta1
Date	August 27, 2004 06:54:24
Msg-id	007c01c48c03$269b50c0$0501a8c0@comai04 Whole thread Raw
Responses	Re: Query performance issue with 8.0.0beta1 Re: Query performance issue with 8.0.0beta1
List	pgsql-performance

Tree view

Hi, I have just installed 8.0.0beta1 and I noticed that some query are slower than 7.4.2 queries.

After:

pg_dump my_database >mydb.sql (from 7.4.2)

psql my_new_database <mydb.sql (to 8.0.0 with COPY instead of INSERT)

FULL VACUUM ANALYZE

***With the old db on 7.4.2***

explain analyze SELECT count(*) FROM "SNS_DATA" WHERE "Data_Arrivo_Campione" BETWEEN '2004-01-01 00:00:00' AND '2004-01-31 23:59:59' AND "Cod_Par" = '17476'

gives

Aggregate (cost=46817.89..46817.89 rows=1 width=0) (actual time=401.216..401.217 rows=1 loops=1)
   -> Index Scan using snsdata_codpar on "SNS_DATA" (cost=0.00..46817.22 rows=268 width=0) (actual time=165.948..400.258 rows=744 loops=1)
         Index Cond: (("Cod_Par")::text = '17476'::text)
         Filter: (("Data_Arrivo_Campione" >= '2004-01-01 00:00:00'::timestamp without time zone) AND ("Data_Arrivo_Campione" <= '2004-01-31 23:59:59'::timestamp without time zone))
Total runtime: 401.302 ms

***while on 8.0.0***

the same query gives

-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Aggregate (cost=93932.91..93932.91 rows=1 width=0) (actual time=14916.371..14916.371 rows=1 loops=1)
-> Seq Scan on "SNS_DATA" (cost=0.00..93930.14 rows=1108 width=0) (actual time=6297.152..14915.330 rows=744 loops=1)
Filter: (("Data_Arrivo_Campione" >= '2004-01-01 00:00:00'::timestamp without time zone) AND ("Data_Arrivo_Campione" <= '2004-01-31 23:59:59'::timestamp without time zone) AND (("Cod_Par")::text = '17476'::text))
Total runtime: 14916.935 ms

And I if disable the seqscan

SET enable_seqscan = false;

I get the following:

Aggregate (cost=158603.19..158603.19 rows=1 width=0) (actual time=4605.862..4605.863 rows=1 loops=1)
   -> Index Scan using snsdata_codpar on "SNS_DATA" (cost=0.00..158600.41 rows=1108 width=0) (actual time=2534.422..4604.865 rows=744 loops=1)
         Index Cond: (("Cod_Par")::text = '17476'::text)
         Filter: (("Data_Arrivo_Campione" >= '2004-01-01 00:00:00'::timestamp without time zone) AND ("Data_Arrivo_Campione" <= '2004-01-31 23:59:59'::timestamp without time zone))
Total runtime: 4605.965 ms

The total runtime is bigger (x10 !!) than the old one.

The memory runtime parameters are

shared_buffer = 2048

work_mem = sort_mem = 2048

SNS_DATA shema is the following:

The table has 2M of records

Can it be a datatype conversion issue?

Can it be depend on the the type of restore (with COPY commands)?

I have no idea.

Thanks in advance!

Reds

pgsql-performance by date:

From: my ho
Date: 27 August 2004, 06:27:24
Subject: Re: postgresql performance with multimedia

From: Gaetano Mendola
Date: 27 August 2004, 08:32:06
Subject: Re: Equivalent praxis to CLUSTERED INDEX?

Query performance issue with 8.0.0beta1 - Mailing list pgsql-performance

Previous

Next