|
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
|
- Re: [PERFORM] Query performance problem in 8.0.0beta1 Stefano Bonnin
- Re: [PERFORM] Query performance problem in 8.0.0beta1 Russell Smith
