From: | "Stefano Bonnin" <stefano(dot)bonnin(at)comai(dot)to> |
---|---|
To: | <pgsql-performance(at)postgresql(dot)org> |
Subject: | Query performance problem in 8.0.0beta1 |
Date: | 2004-08-26 13:36:20 |
Message-ID: | 008a01c48b71$abd89640$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 a FULL VACUUM ANALYZE
***With 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()
Can it be a datatype conversion problem?
Thanks in advance!
Reds
From | Date | Subject | |
---|---|---|---|
Next Message | Tore Halset | 2004-08-26 15:13:24 | Re: Anyone familiar with Apple Xserve RAID |
Previous Message | Andrew Rawnsley | 2004-08-26 12:07:42 | Re: Anyone familiar with Apple Xserve RAID |