pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedQuery performance issue with 8.0.0beta1
6+ messages / 4 participants
[nested] [flat]
* Query performance issue with 8.0.0beta1
@ 2004-08-27 06:57 Stefano Bonnin <stefano.bonnin@comai.to>
0 siblings, 2 replies; 6+ messages in thread
From: Stefano Bonnin @ 2004-08-27 06:57 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:
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 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()
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query performance issue with 8.0.0beta1
@ 2004-08-27 08:48 Russell Smith <mr-russ@pws.com.au>
parent: Stefano Bonnin <stefano.bonnin@comai.to>
1 sibling, 0 replies; 6+ messages in thread
From: Russell Smith @ 2004-08-27 08:48 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query performance issue with 8.0.0beta1
@ 2004-08-27 16:52 andrew@pillette.com
0 siblings, 0 replies; 6+ messages in thread
From: andrew@pillette.com @ 2004-08-27 16:52 UTC (permalink / raw)
To: Stefano Bonnin <stefano.bonnin@comai.to>; +Cc: pgsql-performance
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?
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query performance issue with 8.0.0beta1
@ 2004-08-27 17:14 Josh Berkus <josh@agliodbs.com>
parent: Stefano Bonnin <stefano.bonnin@comai.to>
1 sibling, 1 reply; 6+ messages in thread
From: Josh Berkus @ 2004-08-27 17:14 UTC (permalink / raw)
To: Stefano Bonnin <stefano.bonnin@comai.to>; pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Fw: Query performance issue with 8.0.0beta1
@ 2004-09-01 07:56 Stefano Bonnin <stefano.bonnin@comai.to>
0 siblings, 0 replies; 6+ messages in thread
From: Stefano Bonnin @ 2004-09-01 07:56 UTC (permalink / raw)
To: pgsql-performance
----- Original Message -----
From: "Stefano Bonnin" <stefano.bonnin@comai.to>
To: "Josh Berkus" <josh@agliodbs.com>
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 = 20000 # 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-10000 credits
> #vacuum_cost_page_miss = 10 # 0-10000 credits
> #vacuum_cost_page_dirty = 20 # 0-10000 credits
> #vacuum_cost_limit = 200 # 0-10000 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-100000, 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:
> # debug5, debug4, debug3, debug2,
debug1,
> # info, notice, warning, error, log,
> fatal,
> # panic
>
> #log_error_verbosity = default # terse, default, or verbose messages
>
> #log_min_error_statement = panic # Values in order of increasing severity:
> # debug5, debug4, debug3, debug2,
debug1,
> # info, notice, warning, error,
> panic(off)
>
> #log_min_duration_statement = -1 # -1 is disabled, in milliseconds.
>
> #silent_mode = false # DO NOT USE without syslog or
> redirect_stderr
>
> # - What to Log -
>
> #debug_print_parse = false
> #debug_print_rewritten = false
> #debug_print_plan = false
> #debug_pretty_print = false
> #log_connections = false
> #log_disconnections = false
> #log_duration = false
> #log_line_prefix = '%t %u %d ' # e.g. '<%u%%%d> '
> # %u=user name %d=database name
> # %r=remote host and port
> # %p=PID %t=timestamp %i=command tag
> # %c=session id %l=session line number
> # %s=session start timestamp
> # %x=stop here in non-session processes
> # %%='%'
> log_statement = 'all' # none, mod, ddl, all
> #log_hostname = false
>
>
>
#---------------------------------------------------------------------------
> # RUNTIME STATISTICS
>
#---------------------------------------------------------------------------
>
> # - Statistics Monitoring -
>
> #log_parser_stats = false
> #log_planner_stats = false
> #log_executor_stats = false
> #log_statement_stats = false
>
> # - Query/Index Statistics Collector -
>
> #stats_start_collector = true
> #stats_command_string = false
> #stats_block_level = false
> #stats_row_level = false
> #stats_reset_on_server_start = true
>
#---------------------------------------------------------------------------
> # CLIENT CONNECTION DEFAULTS
>
#---------------------------------------------------------------------------
>
> # - Statement Behavior -
>
> #search_path = '$user,public' # schema names
> #check_function_bodies = true
> #default_transaction_isolation = 'read committed'
> #default_transaction_read_only = false
> #statement_timeout = 0 # 0 is disabled, in milliseconds
>
> # - Locale and Formatting -
>
> #datestyle = 'iso, mdy'
> #timezone = unknown # actually, defaults to TZ environment
> setting
> #australian_timezones = false
> #extra_float_digits = 0 # min -15, max 2
> #client_encoding = sql_ascii # actually, defaults to database encoding
>
> # These settings are initialized by initdb -- they might be changed
> lc_messages = 'it_IT.UTF-8' # locale for system error message
> strings
> lc_monetary = 'it_IT.UTF-8' # locale for monetary formatting
> lc_numeric = 'it_IT.UTF-8' # locale for number formatting
> lc_time = 'it_IT.UTF-8' # locale for time formatting
>
> # - Other Defaults -
>
> #explain_pretty_print = true
> #dynamic_library_path = '$libdir'
>
>
>
#---------------------------------------------------------------------------
> # LOCK MANAGEMENT
>
#---------------------------------------------------------------------------
>
> #deadlock_timeout = 1000 # in milliseconds
> #max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each
>
>
>
#---------------------------------------------------------------------------
> # VERSION/PLATFORM COMPATIBILITY
>
#---------------------------------------------------------------------------
>
> # - Previous Postgres Versions -
> #add_missing_from = true
> #regex_flavor = advanced # advanced, extended, or basic
> #sql_inheritance = true
> #default_with_oids = true
>
> # - Other Platforms & Clients -
>
> #transform_null_equals = false
>
>
>
> ************************
> Thanks
>
> PS. I'm sorry for the late answer but I was not in office.
>
> ----- Original Message -----
> From: "Josh Berkus" <josh@agliodbs.com>
> To: "Stefano Bonnin" <stefano.bonnin@comai.to>;
> <pgsql-performance@postgresql.org>
> Sent: Friday, August 27, 2004 7:14 PM
> Subject: Re: [PERFORM] Query performance issue with 8.0.0beta1
>
>
> > 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
> >
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Query performance issue with 8.0.0beta1
@ 2004-09-01 09:18 Stefano Bonnin <stefano.bonnin@comai.to>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 0 replies; 6+ messages in thread
From: Stefano Bonnin @ 2004-09-01 09:18 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; +Cc: pgsql-performance
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" <josh@agliodbs.com>
To: "Stefano Bonnin" <stefano.bonnin@comai.to>
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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-09-01 09:18 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-08-27 06:57 Query performance issue with 8.0.0beta1 Stefano Bonnin <stefano.bonnin@comai.to>
2004-08-27 08:48 ` Russell Smith <mr-russ@pws.com.au>
2004-08-27 17:14 ` Josh Berkus <josh@agliodbs.com>
2004-09-01 09:18 ` Stefano Bonnin <stefano.bonnin@comai.to>
2004-08-27 16:52 Re: Query performance issue with 8.0.0beta1 andrew@pillette.com
2004-09-01 07:56 Fw: Query performance issue with 8.0.0beta1 Stefano Bonnin <stefano.bonnin@comai.to>
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