Postgres Pro
Downloads
Home   >   mailing lists

Query performance problem in 8.0.0beta1 - Mailing list pgsql-performance

From Stefano Bonnin
Subject Query performance problem in 8.0.0beta1
Date
Msg-id 008a01c48b71$abd89640$0501a8c0@comai04
Whole thread Raw
Responses Re: Query performance problem in 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 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
 
 
 
 
 

pgsql-performance by date:

Previous
From: Mischa Sandberg
Date:
Subject: Re: Equivalent praxis to CLUSTERED INDEX?
Next
From: Mr Pink
Date:
Subject: Re: Why does a simple query not use an obvious index?