Query performance issue with 8.0.0beta1

From: "Stefano Bonnin" <stefano(dot)bonnin(at)comai(dot)to>
To: <pgsql-performance(at)postgresql(dot)org>
Subject: Query performance issue with 8.0.0beta1
Date: 2004-08-27 06:57:43
Message-ID: 007c01c48c03$269b50c0$0501a8c0@comai04
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

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:

Table "public.SNS_DATA"
Column | Type | Modifiers
----------------------+-----------------------------+--------------------
Ordine | integer | not null default 0
Cod_Par | character varying(100) | not null
Cod_Ana | character varying(100) | not null
Valore | character varying(255) |
Descriz | character varying(512) |
Un_Mis | character varying(70) |
hash | integer |
valid | boolean | default true
alarm | boolean | default false
Cod_Luogo | character varying(30) |
Data_Arrivo_Campione | timestamp without time zone |
site_id | integer |
Cod_Luogo_v | character varying(30) |
repeated_val | boolean | default false
Indexes:
"sns_data2_pkey" PRIMARY KEY, btree ("Ordine", "Cod_Ana", "Cod_Par")
"sns_datacodluogo2" btree ("Cod_Luogo")
"sns_datatimefield2" btree ("Data_Arrivo_Campione")
"sns_siteid2" btree (site_id)
"sns_valid2" btree ("valid")
"snsdata_codana" btree ("Cod_Ana")
"snsdata_codpar" btree ("Cod_Par")
Foreign-key constraints:
"$2" FOREIGN KEY ("Cod_Ana") REFERENCES "SNS_ANA"("Cod_Ana") ON DELETE CASCADE
Triggers:
sns_action_tr BEFORE INSERT OR UPDATE ON "SNS_DATA" FOR EACH ROW EXECUTE PROCEDURE sns_action()

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

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Gaetano Mendola 2004-08-27 08:26:26 Re: Equivalent praxis to CLUSTERED INDEX?
Previous Message my ho 2004-08-27 06:27:16 Re: postgresql performance with multimedia