Fw: [PERFORM] Query performance issue with 8.0.0beta1

2004-09-01 Thread Stefano Bonnin 

- Original Message - 
From: Stefano Bonnin [EMAIL PROTECTED]
To: Josh Berkus [EMAIL PROTECTED]
Sent: Monday, August 30, 2004 4:13 PM
Subject: Re: [PERFORM] Query performance issue with 8.0.0beta1


 This is my postgres.conf, I have changed only the work_mem and
 shared_buffers parameters.

 DID you
  configure it for the 8.0 database?

 What does it mean? Is in 8.0 some important NEW configation parameter ?

 # pgdata = '/usr/local/pgsql/data'  # use data in another
 directory
 # hba_conf = '/etc/pgsql/pg_hba.conf'   # use hba info in another
 directory
 # ident_conf = '/etc/pgsql/pg_ident.conf'   # use ident info in
another
 directory
 # external_pidfile= '/var/run/postgresql.pid'   # write an extra pid file
 #listen_addresses = 'localhost' # what IP interface(s) to listen on;
 # defaults to localhost, '*' = any
 #port = 5432
 max_connections = 100
 #superuser_reserved_connections = 2
 #unix_socket_directory = ''
 #unix_socket_group = ''
 #unix_socket_permissions = 0777 # octal
 #rendezvous_name = ''   # defaults to the computer name
 #authentication_timeout = 60# 1-600, in seconds
 #ssl = false
 #password_encryption = true
 #krb_server_keyfile = ''
 #db_user_namespace = false

 shared_buffers = 2048   # min 16, at least max_connections*2, 8KB
 each
 work_mem = 2048 # min 64, size in KB
 #maintenance_work_mem = 16384   # min 1024, size in KB
 #max_stack_depth = 2048 # min 100, size in KB

 #max_fsm_pages = 2  # min max_fsm_relations*16, 6 bytes each
 #max_fsm_relations = 1000   # min 100, ~50 bytes each

 #max_files_per_process = 1000   # min 25
 #preload_libraries = ''

 #vacuum_cost_delay = 0  # 0-1000 milliseconds
 #vacuum_cost_page_hit = 1   # 0-1 credits
 #vacuum_cost_page_miss = 10 # 0-1 credits
 #vacuum_cost_page_dirty = 20# 0-1 credits
 #vacuum_cost_limit = 200# 0-1 credits

 #bgwriter_delay = 200   # 10-5000 milliseconds
 #bgwriter_percent = 1   # 1-100% of dirty buffers
 #bgwriter_maxpages = 100# 1-1000 buffers max at once

 #fsync = true   # turns forced synchronization on or off
 #wal_sync_method = fsync# the default varies across platforms:
 # fsync, fdatasync, open_sync, or
 open_datasync
 #wal_buffers = 8# min 4, 8KB each
 #commit_delay = 0   # range 0-10, in microseconds
 #commit_siblings = 5# range 1-100
 #checkpoint_segments = 3# in logfile segments, min 1, 16MB each
 #checkpoint_timeout = 300   # range 30-3600, in seconds
 #checkpoint_warning = 30# 0 is off, in seconds

 #archive_command = ''   # command to use to archive a logfile
 segment

 #enable_hashagg = true
 #enable_hashjoin = true
 #enable_indexscan = true
 #enable_mergejoin = true
 #enable_nestloop = true
 #enable_seqscan = true
 #enable_sort = true
 #enable_tidscan = true

 #effective_cache_size = 1000# typically 8KB each
 #random_page_cost = 4   # units are one sequential page fetch cost
 #cpu_tuple_cost = 0.01  # (same)
 #cpu_index_tuple_cost = 0.001   # (same)
 #cpu_operator_cost = 0.0025 # (same)

 #geqo = true
 #geqo_threshold = 12
 #geqo_effort = 5# range 1-10
 #geqo_pool_size = 0 # selects default based on effort
 #geqo_generations = 0   # selects default based on effort
 #geqo_selection_bias = 2.0  # range 1.5-2.0

 default_statistics_target = 20  # range 1-1000
 #from_collapse_limit = 8
 #join_collapse_limit = 8# 1 disables collapsing of explicit JOINs

 #log_destination = 'stderr' # Valid values are combinations of stderr,
 # syslog and eventlog, depending on
 # platform.

 # This is relevant when logging to stderr:
 #redirect_stderr = false# Enable capturing of stderr into log files.
 # These are only relevant if redirect_stderr is true:
 #log_directory = 'pg_log'   # Directory where logfiles are written.
 # May be specified absolute or relative to
 PGDATA
 #log_filename_prefix = 'postgresql_' # Prefix for logfile names.
 #log_rotation_age = 1440# Automatic rotation of logfiles will happen
 after
 # so many minutes.  0 to disable.
 #log_rotation_size = 10240  # Automatic rotation of logfiles will happen
 after
 # so many kilobytes of log output.  0 to
 disable.

 # These are relevant when logging to syslog:
 #syslog_facility = 'LOCAL0'
 #syslog_ident = 'postgres'


 # - When to Log -

 #client_min_messages = notice   # Values, in order of decreasing detail:
 #   debug5, debug4, debug3, debug2,
debug1,
 #   log, notice, warning, error

 #log_min_messages = notice  # Values, in order of decreasing detail

Re: [PERFORM] Query performance issue with 8.0.0beta1

2004-09-01 Thread Stefano Bonnin 
Server HP: Intel(R) Pentium(R) 4 CPU 2.26GHz
RAM 1GB
OS: RedHat 8
And the disk:

kernel: megaide: driver version 05.04f (Date: Nov 11, 2002; 18:15 EST)
kernel: megaide: bios version 02.06.07221605
kernel: megaide: LD 0 RAID1 status=ONLINE sectors=156297343
capacity=76317 MB drives=2
kernel: scsi0 : LSI Logic MegaIDE RAID BIOS Version 2.6.07221605, 8
targs 1 chans 8 luns
kernel:   Vendor: LSILOGIC  Model:  LD  0  IDERAID   Rev:
kernel:   Type:   Direct-Access  ANSI SCSI revision:
02

Thanks.
RedS


- Original Message - 
From: Josh Berkus [EMAIL PROTECTED]
To: Stefano Bonnin [EMAIL PROTECTED]
Sent: Monday, August 30, 2004 6:54 PM
Subject: Re: [PERFORM] Query performance issue with 8.0.0beta1


 Stefano,

  This is my postgres.conf, I have changed only the work_mem and
  shared_buffers parameters.

 And not very much, I see.

  DID you
   configure it for the 8.0 database?
 
  What does it mean? Is in 8.0 some important NEW configation parameter ?

 Well, there are but they shouldn't affect your case. However, there
are a
 lot of other settings that need to be adjusted.   Please post your
hardware
 plaform information:  OS, CPU, RAM, and disk setup.

 -- 
 Josh Berkus
 Aglio Database Solutions
 San Francisco



---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

[PERFORM] Query performance issue with 8.0.0beta1

2004-08-27 Thread Stefano Bonnin 





Hi, I have just installed 8.0.0beta1 and I noticed 
that some query are slower than 7.4.2 queries.

After:
pg_dump my_database mydb.sql (from 
7.4.2)
psql my_new_database mydb.sql (to 8.0.0 with COPY instead of 
INSERT)
FULL VACUUM ANALYZE

***With the old db on 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 
0Cod_Par 
| character varying(100) | not 
nullCod_Ana 
| character varying(100) | not 
nullValore 
| character varying(255) 
|Descriz 
| character varying(512) 
|Un_Mis 
| character varying(70) 
|hash 
| 
integer 
|valid 
| 
boolean 
| default 
truealarm 
| 
boolean 
| default 
falseCod_Luogo 
| character varying(30) 
|Data_Arrivo_Campione | timestamp without time zone 
|site_id 
| 
integer 
|Cod_Luogo_v | 
character varying(30) 
|repeated_val | 
boolean 
| default falseIndexes: "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 
CASCADETriggers: sns_action_tr BEFORE INSERT OR UPDATE 
ON "SNS_DATA" FOR EACH ROW EXECUTE PROCEDURE sns_action()

The table has 2M of records
Can it be a datatype conversion issue?
Can it be depend on the the type of restore (with COPY commands)?
I have no idea.

Thanks in advance!
Reds

Re: [PERFORM] Query performance issue with 8.0.0beta1

2004-08-27 Thread Russell Smith 
7.4.2
  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
 
Row counts are out by a factor of 3, on the low side. so the planner will guess index 
is better, which it is.

 ***while on 8.0.0***
  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
Planner guesses that 1108 row should be returned, which is out by less, but on the 
high side.
Big question is given there are 2M rows, why does returning 1108 rows, less than 1% 
result in a sequence scan.
Usually the selectivity on the index is bad, try increasing the stats target on the 
column.

I know 8.0 has new stats anaylsis code, which could be effecting how it choses the 
plan. But it would still
require a good amount of stats to get it to guess correctly.

Increase stats and see if the times improve.

 
 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.
Did you run this multiple times, or is this the first time.  If it had to get the data 
off disk it will be slower.
Are you sure that it's coming from disk in this and the 7.4 case? or both from memory.
If 7.4 is from buffer_cache, or kernel_cache, and 8.0 is from disk you are likely to 
get A LOT slower.

 
 The memory runtime parameters are 
 shared_buffer = 2048
 work_mem = sort_mem = 2048
 
[ snip ]

 The table has 2M of records
 Can it be a datatype conversion issue?
That should not be an issue in 8.0, at least for the simple type conversions.  like 
int8 to int4.
I'm not 100% sure which ones were added, and which were not, but the query appears to 
cast everything correctly anyway.

 Can it be depend on the the type of restore (with COPY commands)?
Shouldn't and VACUUM FULL ANALYZE will make the table as small as possible.  The row 
order may be different
on disk, but the planner won't know that, and it's a bad plan causing the problem.

 I have no idea.
 
 Thanks in advance!
 Reds
 
Regards

Russell Smith.

---(end of broadcast)---
TIP 7: don't forget to increase your free space map settings

Re: [PERFORM] Query performance issue with 8.0.0beta1

2004-08-27 Thread andrew 
Is it possible (to mix two threads) that you had CLUSTERed the table on the old 
database in a way that retrieved the records in this query faster?

---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster

Re: [PERFORM] Query performance issue with 8.0.0beta1

2004-08-27 Thread Josh Berkus 
Stefano,

 Hi, I have just installed 8.0.0beta1 and I noticed that some query are
 slower than 7.4.2 queries.

Seems unlikely.   How have you configured postgresql.conf?DID you 
configure it for the 8.0 database?

-- 
Josh Berkus
Aglio Database Solutions
San Francisco

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match