Postgres Pro
Downloads
Home   >   mailing lists

Thread: Query performance problem in 8.0.0beta1

Query performance problem in 8.0.0beta1

From
"Stefano Bonnin"
Date:
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: Query performance problem in 8.0.0beta1

From
Russell Smith
Date:
Hi,

I assume you have reposted because you have just signed up to the list.

If this is the case, can you please read the archives or replies to your original post about this question.
It did make it onto the archives and myself and others did reply with a few ideas and questions.

If you could address those in a reply mail that would help everybody with your problem

Regards

Russell Smith