pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Query performance problem in 8.0.0beta1
2+ messages / 2 participants
[nested] [flat]

* Query performance problem in 8.0.0beta1
@ 2004-08-26 13:36 Stefano Bonnin <stefano.bonnin@comai.to>
  2004-08-29 22:31 ` Re: Query performance problem in 8.0.0beta1 Russell Smith <mr-russ@pws.com.au>
  0 siblings, 1 reply; 2+ messages in thread

From: Stefano Bonnin @ 2004-08-26 13:36 UTC (permalink / raw)
  To: 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

^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Query performance problem in 8.0.0beta1
  2004-08-26 13:36 Query performance problem in 8.0.0beta1 Stefano Bonnin <stefano.bonnin@comai.to>
@ 2004-08-29 22:31 ` Russell Smith <mr-russ@pws.com.au>
  0 siblings, 0 replies; 2+ messages in thread

From: Russell Smith @ 2004-08-29 22:31 UTC (permalink / raw)
  To: pgsql-performance

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



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2004-08-29 22:31 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-26 13:36 Query performance problem in 8.0.0beta1 Stefano Bonnin <stefano.bonnin@comai.to>
2004-08-29 22:31 ` Russell Smith <mr-russ@pws.com.au>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox