Thread: slow database

slow database

From
alemon@tiago.hazor.com.br
Date:

my data base is very slow. The machine is a processor Xeon 2GB with
256 MB of RAM DDR. My archive of configuration is this:

================================================================

#
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
#   name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Any option can also be given as a command line switch to the
# postmaster, e.g. 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload".
#========================================================================


#
#       Connection Parameters
#
#tcpip_socket = false
#ssl = false

max_connections = 50
superuser_reserved_connections = 2

#port = 5432
#hostname_lookup = false
#show_source_port = false

#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal

#virtual_host = ''

#krb_server_keyfile = ''

#
#       Shared Memory Size
#
shared_buffers = 5000           # min max_connections*2 or 16, 8KB each
max_fsm_relations = 400         # min 10, fsm is free space map, ~40 bytes
max_fsm_pages = 80000           # min 1000, fsm is free space map, ~6 bytes
max_locks_per_transaction = 128 # min 10
wal_buffers = 4         # min 4, typically 8KB each

#
#       Non-shared Memory Sizes
#
sort_mem = 131072               # min 64, size in KB
#vacuum_mem = 8192              # min 1024, size in KB


#
#       Write-ahead log (WAL)
#
checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 300        # range 30-3600, in seconds
#
commit_delay = 0                # range 0-100000, in microseconds
commit_siblings = 5             # range 1-1000
#
fsync = false
wal_sync_method = fdatasync     # the default varies across platforms:
#                               # fsync, fdatasync, open_sync, or open_datasync
wal_debug = 0                   # range 0-16


#
#       Optimizer Parameters
#
enable_seqscan = false
enable_indexscan = false
enable_tidscan = false
enable_sort = false
enable_nestloop = false
enable_mergejoin = false
enable_hashjoin = false

effective_cache_size = 170000   # typically 8KB each
random_page_cost = 1000000000   # units are one sequential page fetch cost
cpu_tuple_cost = 0.3            # (same)
cpu_index_tuple_cost = 0.6      # (same)
cpu_operator_cost = 0.7         # (same)

default_statistics_target = 1   # range 1-1000

#
#       GEQO Optimizer Parameters
#
geqo = true
geqo_selection_bias = 2.0       # range 1.5-2.0
geqo_threshold = 2000
geqo_pool_size = 1024           # default based on tables in statement,
                                # range 128-1024
geqo_effort = 1
geqo_generations = 0
geqo_random_seed = -1           # auto-compute seed


#
#       Message display
#
server_min_messages = fatal     # Values, in order of decreasing detail:
                                #   debug5, debug4, debug3, debug2, debug1,
                                #   info, notice, warning, error, log, fatal,
                                #   panic
client_min_messages = fatal     # Values, in order of decreasing detail:
                                #   debug5, debug4, debug3, debug2, debug1,
                                #   log, info, notice, warning, error
silent_mode = false

log_connections = false
log_pid = false
log_statement = false
log_duration = false
log_timestamp = false

#log_min_error_statement = error # Values in order of increasing severity:
                                 #   debug5, debug4, debug3, debug2, debug1,
                                 #   info, notice, warning, error, panic(off)


#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false

#explain_pretty_print = true

# requires USE_ASSERT_CHECKING
#debug_assertions = true


#
#       Syslog
#
#syslog = 0                     # range 0-2
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'


#
#       Statistics
#
#show_parser_stats = false
#show_planner_stats = false
#show_executor_stats = false
#show_statement_stats = false

# requires BTREE_BUILD_STATS
#show_btree_build_stats = false


#
#       Access statistics collection
#
#stats_start_collector = true
#stats_reset_on_server_start = true
#stats_command_string = false
#stats_row_level = false
#stats_block_level = false
#
#       Lock Tracing
#
#trace_notify = false

# requires LOCK_DEBUG
#trace_locks = false
#trace_userlocks = false
#trace_lwlocks = false
#debug_deadlocks = false
#trace_lock_oidmin = 16384
#trace_lock_table = 0


#
#       Misc
#
#autocommit = true
#dynamic_library_path = '$libdir'
#search_path = '$user,public'
datestyle = 'iso, us'
#timezone = unknown             # actually, defaults to TZ environment setting
#australian_timezones = false
client_encoding = sql_ascii     # actually, defaults to database encoding
authentication_timeout = 1      # 1-600, in seconds
deadlock_timeout = 100          # in milliseconds
#default_transaction_isolation = 'read committed'
#max_expr_depth = 1000          # min 10
#max_files_per_process = 1000   # min 25
#password_encryption = true
#sql_inheritance = true
#transform_null_equals = false
#statement_timeout = 0          # 0 is disabled, in milliseconds
db_user_namespace = false



#
#       Locale settings
#
# (initialized by initdb -- may be changed)
LC_MESSAGES = 'en_US'
LC_MONETARY = 'en_US'
LC_NUMERIC = 'en_US'
LC_TIME = 'en_US'


================================================================


somebody please knows to give tips to me to increase the
performance

Re: slow database

From
Dennis Bjorklund
Date:
On Wed, 11 Feb 2004 alemon@tiago.hazor.com.br wrote:

> somebody please knows to give tips to me to increase the
> performance

Run VACUUM ANALYZE. Find one query that is slow. Run EXPLAIN ANALYZE on
that query. Read the plan and figure out why it is slow. Fix it.

--
/Dennis Björklund


Re: slow database

From
PC Drew
Date:
On Feb 11, 2004, at 7:23 AM, alemon@tiago.hazor.com.br wrote:

> #
> #       Optimizer Parameters
> #
> enable_seqscan = false
> enable_indexscan = false
> enable_tidscan = false
> enable_sort = false
> enable_nestloop = false
> enable_mergejoin = false
> enable_hashjoin = false
>

Why did you disable *every* type of query method?  Try commenting all
of these out or changing them to "true" instead of "false".

--
PC Drew
Manager, Dominet

IBSN
1600 Broadway, Suite 400
Denver, CO 80202

Phone: 303-984-4727 x107
Cell: 720-841-4543
Fax: 303-984-4730
Email: drewpc@ibsncentral.com


Re: slow database

From
PC Drew
Date:
On Feb 11, 2004, at 7:23 AM, alemon@tiago.hazor.com.br wrote:

>
>
> my data base is very slow. The machine is a processor Xeon 2GB with
> 256 MB of RAM DDR. My archive of configuration is this:
>

After looking through the configuration some more, I would definitely
recommend getting rid of your current postgresql.conf file and
replacing it with the default.  You have some very very odd settings,
namely:

This is dangerous, but maybe you need it:
fsync = false

You've essentially disabled the optimizer:
enable_seqscan = false
enable_indexscan = false
enable_tidscan = false
enable_sort = false
enable_nestloop = false
enable_mergejoin = false
enable_hashjoin = false

WOAH, this is huge:
random_page_cost = 1000000000

Take a look at this page which goes through each option in the
configuration file:

http://www.varlena.com/varlena/GeneralBits/Tidbits/annotated_conf_e.html

--
PC Drew
Manager, Dominet

IBSN
1600 Broadway, Suite 400
Denver, CO 80202

Phone: 303-984-4727 x107
Cell: 720-841-4543
Fax: 303-984-4730
Email: drewpc@ibsncentral.com


Re: slow database

From
Rod Taylor
Date:
On Wed, 2004-02-11 at 09:23, alemon@tiago.hazor.com.br wrote:
> my data base is very slow. The machine is a processor Xeon 2GB with
> 256 MB of RAM DDR. My archive of configuration is this:

I'm not surprised. New values below old.


> sort_mem = 131072               # min 64, size in KB

sort_mem = 8192.

> fsync = false

Are you aware of the potential for data corruption during a hardware,
power or software failure?

> enable_seqscan = false
> enable_indexscan = false
> enable_tidscan = false
> enable_sort = false
> enable_nestloop = false
> enable_mergejoin = false
> enable_hashjoin = false

You want all of these set to true, not false.

> effective_cache_size = 170000   # typically 8KB each

effective_cache_size =  16384.

> random_page_cost = 1000000000   # units are one sequential page fetch cost

random_page_cost = 3

> cpu_tuple_cost = 0.3            # (same)

cpu_tuple_cost = 0.01

> cpu_index_tuple_cost = 0.6      # (same)

cpu_index_tuple_cost = 0.001

> cpu_operator_cost = 0.7         # (same)

cpu_operator_cost = 0.0025

> default_statistics_target = 1   # range 1-1000

default_statistics_target = 10



Re: slow database

From
Chris Trawick
Date:
alemon@tiago.hazor.com.br wrote:

>my data base is very slow. The machine is a processor Xeon 2GB with
>256 MB of RAM DDR. My archive of configuration is this:
>
>

This is a joke, right?

chris

Re: slow database

From
alemon@tiago.hazor.com.br
Date:
the normal queries do not present problems, but all the ones
that join has are very slow.

OBS: I am using way ODBC. He will be that they exist some
configuration specifies inside of the same bank or in the ODBC?


Quoting Rod Taylor <pg@rbt.ca>:

> On Wed, 2004-02-11 at 09:23, alemon@tiago.hazor.com.br wrote:
> > my data base is very slow. The machine is a processor Xeon 2GB with
> > 256 MB of RAM DDR. My archive of configuration is this:
>
> I'm not surprised. New values below old.
>
>
> > sort_mem = 131072               # min 64, size in KB
>
> sort_mem = 8192.
>
> > fsync = false
>
> Are you aware of the potential for data corruption during a hardware,
> power or software failure?
>
> > enable_seqscan = false
> > enable_indexscan = false
> > enable_tidscan = false
> > enable_sort = false
> > enable_nestloop = false
> > enable_mergejoin = false
> > enable_hashjoin = false
>
> You want all of these set to true, not false.
>
> > effective_cache_size = 170000   # typically 8KB each
>
> effective_cache_size =  16384.
>
> > random_page_cost = 1000000000   # units are one sequential page fetch cost
>
> random_page_cost = 3
>
> > cpu_tuple_cost = 0.3            # (same)
>
> cpu_tuple_cost = 0.01
>
> > cpu_index_tuple_cost = 0.6      # (same)
>
> cpu_index_tuple_cost = 0.001
>
> > cpu_operator_cost = 0.7         # (same)
>
> cpu_operator_cost = 0.0025
>
> > default_statistics_target = 1   # range 1-1000
>
> default_statistics_target = 10
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>




Re: slow database

From
Chris Browne
Date:
alemon@tiago.hazor.com.br writes:
> my data base is very slow. The machine is a processor Xeon 2GB with
> 256 MB of RAM DDR. My archive of configuration is this:

> sort_mem = 131072               # min 64, size in KB
> #vacuum_mem = 8192              # min 1024, size in KB

Change it back to 8192, or perhaps even less.  This large value is
probably causing swapping, because it leads to every sort trying to
use 1073741824 bytes of memory, which is considerably more than you
have.

> fsync = false
> wal_sync_method = fdatasync     # the default varies across platforms:

I presume that you are aware that you have chosen the value that
leaves your data vulnerable to corruption?  I wouldn't set this to false...

> enable_seqscan = false
> enable_indexscan = false
> enable_tidscan = false
> enable_sort = false
> enable_nestloop = false
> enable_mergejoin = false
> enable_hashjoin = false

Was there some reason why you wanted to disable every query
optimization strategy that can be disabled?  If you're looking to get
slow queries, this would accomplish that nicely.

> effective_cache_size = 170000   # typically 8KB each
> random_page_cost = 1000000000   # units are one sequential page fetch cost
> cpu_tuple_cost = 0.3            # (same)
> cpu_index_tuple_cost = 0.6      # (same)
> cpu_operator_cost = 0.7         # (same)

Where did you get those numbers?  The random_page_cost alone will
probably force every query to do seq scans, ignoring indexes, and is
_really_ nonsensical.  The other values seem way off.

> default_statistics_target = 1   # range 1-1000

... Apparently it didn't suffice to try to disable query optimization,
and modify the cost parameters into nonsense; it was also "needful" to
tell the statistics analyzer to virtually eliminate statistics
collection.

If you want a value other than 10, then pick a value slightly LARGER than 10.

> somebody please knows to give tips to me to increase the performance

Delete the postgresql.conf file, create a new database using initdb,
and take the file produced by _that_, and replace with that one.  The
default values, while not necessarily perfect, are likely to be 100x
better than what you have got.

Was this the result of someone trying to tune the database for some
sort of anti-benchmark?
--
let name="cbbrowne" and tld="cbbrowne.com" in name ^ "@" ^ tld;;
http://www.ntlug.org/~cbbrowne/rdbms.html
Rules  of  the  Evil  Overlord   #179.  "I  will  not  outsource  core
functions." <http://www.eviloverlord.com/>

Re: slow database

From
Tom Lane
Date:
alemon@tiago.hazor.com.br writes:
> the normal queries do not present problems, but all the ones
> that join has are very slow.

No surprise, as you've disabled all but the stupidest join algorithm...

As others already pointed out, you'd be a lot better off with the
default configuration settings than with this set.

            regards, tom lane

Re: slow database

From
"scott.marlowe"
Date:
If my boss came to me and asked me to make my database server run as
slowly as possible, I might come up with the exact same postgresql.conf
file as what you posted.

Just installing the default postgresql.conf that came with postgresql
should make this machine run faster.

Read this:

http://www.varlena.com/varlena/GeneralBits/Tidbits/perf.html


Re: slow database

From
alemon@tiago.hazor.com.br
Date:
I already came back the old conditions and I continue slow in the same
way!

Quoting Tom Lane <tgl@sss.pgh.pa.us>:

> alemon@tiago.hazor.com.br writes:
> > the normal queries do not present problems, but all the ones
> > that join has are very slow.
>
> No surprise, as you've disabled all but the stupidest join algorithm...
>
> As others already pointed out, you'd be a lot better off with the
> default configuration settings than with this set.
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>
>




Re: slow database

From
alemon@tiago.hazor.com.br
Date:
the version is 7.3.2 in a connective 9.
the hen foot is without nails at the moment: =)
 |
/|\

this is a principal table of system:

CREATE TABLE public.compra_prod_forn
(
  nu_seq_prod_forn numeric(12) NOT NULL,
  cd_fabricante numeric(6),
  cd_moeda numeric(4) NOT NULL,
  cd_compra numeric(12) NOT NULL,
  cd_produto numeric(9),
  cd_fornecedor numeric(6),
  cd_incotermes numeric(3) NOT NULL,
  qtde_compra numeric(12,3),
  perc_comissao_holding numeric(5,2),
  vl_cotacao_unit_negociacao numeric(20,3),
  dt_retorno date,
  cd_status_cv numeric(3) NOT NULL,
  cd_usuario numeric(6) NOT NULL,
  tp_comissao varchar(25),
  vl_pif numeric(9,2),
  cd_fornecedor_contato numeric(6),
  cd_contato numeric(6),
  cd_un_peso varchar(20),
  vl_currier numeric(9,2),
  cd_iqf numeric(3),
  cd_un_peso_vl_unit varchar(10),
  dt_def_fornecedor date,
  vl_cotacao_unit_forn numeric(20,3),
  vl_cotacao_unit_local numeric(20,3),
  tp_vl_cotacao_unit numeric(1),
  cd_moeda_forn numeric(4),
  cd_moeda_local numeric(4),
  vl_cotacao_unit numeric(20,3),
  peso_bruto_emb varchar(20),
  id_fax numeric(1),
  id_email numeric(1),
  fob varchar(40),
  origem varchar(40),
  tipo_comissao varchar(40),
  descr_fabricante_select varchar(200),
  farmacopeia varchar(100),
  vl_frete numeric(10,3),
  descr_abandono_representada varchar(2000),
  descr_abandono_interno varchar(2000),
  vl_frete_unit numeric(10,3),
  CONSTRAINT compra_prod_forn_pkey PRIMARY KEY (cd_compra, nu_seq_prod_forn),
  CONSTRAINT "$1" FOREIGN KEY (cd_moeda_local) REFERENCES public.moeda
(cd_moeda) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "$10" FOREIGN KEY (cd_usuario) REFERENCES public.usuario_sistema
(cd_usuario) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "$11" FOREIGN KEY (cd_status_cv) REFERENCES
public.status_compra_venda (cd_status_cv) ON UPDATE NO ACTION ON DELETE NO
ACTION,
  CONSTRAINT "$12" FOREIGN KEY (cd_moeda) REFERENCES public.moeda (cd_moeda) ON
UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "$2" FOREIGN KEY (cd_moeda_forn) REFERENCES public.moeda
(cd_moeda) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "$3" FOREIGN KEY (cd_un_peso_vl_unit) REFERENCES
public.unidades_peso (cd_un_peso) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "$4" FOREIGN KEY (cd_un_peso) REFERENCES public.unidades_peso
(cd_un_peso) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "$5" FOREIGN KEY (cd_fornecedor_contato, cd_contato) REFERENCES
public.fornecedor_contato (cd_fornecedor, cd_contato) ON UPDATE NO ACTION ON
DELETE NO ACTION,
  CONSTRAINT "$6" FOREIGN KEY (cd_fabricante) REFERENCES public.fabricante
(cd_fabricante) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "$7" FOREIGN KEY (cd_produto, cd_fornecedor) REFERENCES
public.fornecedor_produto (cd_produto, cd_fornecedor) ON UPDATE NO ACTION ON
DELETE NO ACTION,
  CONSTRAINT "$8" FOREIGN KEY (cd_incotermes) REFERENCES public.incotermes
(cd_incotermes) ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT "$9" FOREIGN KEY (cd_compra) REFERENCES public.compra (cd_compra)
ON UPDATE NO ACTION ON DELETE CASCADE
) WITH OIDS;



Quoting "scott.marlowe" <scott.marlowe@ihs.com>:

> On Wed, 11 Feb 2004 alemon@tiago.hazor.com.br wrote:
>
> > I already came back the old conditions and I continue slow in the same
> > way!
>
> OK, we need some things from you to help troubleshoot this problem.
>
> Postgresql version
> schema of your tables
> output of "explain analyze your query here"
> a chicken foot (haha, just kidding. :-)
>
>




Re: slow database

From
"scott.marlowe"
Date:
On Wed, 11 Feb 2004 alemon@tiago.hazor.com.br wrote:

> I already came back the old conditions and I continue slow in the same
> way!

OK, we need some things from you to help troubleshoot this problem.

Postgresql version
schema of your tables
output of "explain analyze your query here"
a chicken foot (haha, just kidding. :-)


Re: slow database

From
Rod Taylor
Date:
On Wed, 2004-02-11 at 12:15, alemon@tiago.hazor.com.br wrote:
> I already came back the old conditions and I continue slow in the same
> way!

Dumb question, but did you restart the database after changing the
config file?


Re: slow database

From
"scott.marlowe"
Date:
First thing I would check is to make sure all those foreign keys are the
same type.

Second, make sure you've got indexes to go with them.  I.e. on a multi-key
fk, have a multi-key index.


On Wed, 11 Feb 2004 alemon@tiago.hazor.com.br wrote:

> the version is 7.3.2 in a connective 9.
> the hen foot is without nails at the moment: =)
>  |
> /|\
>
> this is a principal table of system:
>
> CREATE TABLE public.compra_prod_forn
> (
>   nu_seq_prod_forn numeric(12) NOT NULL,
>   cd_fabricante numeric(6),
>   cd_moeda numeric(4) NOT NULL,
>   cd_compra numeric(12) NOT NULL,
>   cd_produto numeric(9),
>   cd_fornecedor numeric(6),
>   cd_incotermes numeric(3) NOT NULL,
>   qtde_compra numeric(12,3),
>   perc_comissao_holding numeric(5,2),
>   vl_cotacao_unit_negociacao numeric(20,3),
>   dt_retorno date,
>   cd_status_cv numeric(3) NOT NULL,
>   cd_usuario numeric(6) NOT NULL,
>   tp_comissao varchar(25),
>   vl_pif numeric(9,2),
>   cd_fornecedor_contato numeric(6),
>   cd_contato numeric(6),
>   cd_un_peso varchar(20),
>   vl_currier numeric(9,2),
>   cd_iqf numeric(3),
>   cd_un_peso_vl_unit varchar(10),
>   dt_def_fornecedor date,
>   vl_cotacao_unit_forn numeric(20,3),
>   vl_cotacao_unit_local numeric(20,3),
>   tp_vl_cotacao_unit numeric(1),
>   cd_moeda_forn numeric(4),
>   cd_moeda_local numeric(4),
>   vl_cotacao_unit numeric(20,3),
>   peso_bruto_emb varchar(20),
>   id_fax numeric(1),
>   id_email numeric(1),
>   fob varchar(40),
>   origem varchar(40),
>   tipo_comissao varchar(40),
>   descr_fabricante_select varchar(200),
>   farmacopeia varchar(100),
>   vl_frete numeric(10,3),
>   descr_abandono_representada varchar(2000),
>   descr_abandono_interno varchar(2000),
>   vl_frete_unit numeric(10,3),
>   CONSTRAINT compra_prod_forn_pkey PRIMARY KEY (cd_compra, nu_seq_prod_forn),
>   CONSTRAINT "$1" FOREIGN KEY (cd_moeda_local) REFERENCES public.moeda
> (cd_moeda) ON UPDATE NO ACTION ON DELETE NO ACTION,
>   CONSTRAINT "$10" FOREIGN KEY (cd_usuario) REFERENCES public.usuario_sistema
> (cd_usuario) ON UPDATE NO ACTION ON DELETE NO ACTION,
>   CONSTRAINT "$11" FOREIGN KEY (cd_status_cv) REFERENCES
> public.status_compra_venda (cd_status_cv) ON UPDATE NO ACTION ON DELETE NO
> ACTION,
>   CONSTRAINT "$12" FOREIGN KEY (cd_moeda) REFERENCES public.moeda (cd_moeda) ON
> UPDATE NO ACTION ON DELETE NO ACTION,
>   CONSTRAINT "$2" FOREIGN KEY (cd_moeda_forn) REFERENCES public.moeda
> (cd_moeda) ON UPDATE NO ACTION ON DELETE NO ACTION,
>   CONSTRAINT "$3" FOREIGN KEY (cd_un_peso_vl_unit) REFERENCES
> public.unidades_peso (cd_un_peso) ON UPDATE NO ACTION ON DELETE NO ACTION,
>   CONSTRAINT "$4" FOREIGN KEY (cd_un_peso) REFERENCES public.unidades_peso
> (cd_un_peso) ON UPDATE NO ACTION ON DELETE NO ACTION,
>   CONSTRAINT "$5" FOREIGN KEY (cd_fornecedor_contato, cd_contato) REFERENCES
> public.fornecedor_contato (cd_fornecedor, cd_contato) ON UPDATE NO ACTION ON
> DELETE NO ACTION,
>   CONSTRAINT "$6" FOREIGN KEY (cd_fabricante) REFERENCES public.fabricante
> (cd_fabricante) ON UPDATE NO ACTION ON DELETE NO ACTION,
>   CONSTRAINT "$7" FOREIGN KEY (cd_produto, cd_fornecedor) REFERENCES
> public.fornecedor_produto (cd_produto, cd_fornecedor) ON UPDATE NO ACTION ON
> DELETE NO ACTION,
>   CONSTRAINT "$8" FOREIGN KEY (cd_incotermes) REFERENCES public.incotermes
> (cd_incotermes) ON UPDATE NO ACTION ON DELETE NO ACTION,
>   CONSTRAINT "$9" FOREIGN KEY (cd_compra) REFERENCES public.compra (cd_compra)
> ON UPDATE NO ACTION ON DELETE CASCADE
> ) WITH OIDS;
>
>
>
> Quoting "scott.marlowe" <scott.marlowe@ihs.com>:
>
> > On Wed, 11 Feb 2004 alemon@tiago.hazor.com.br wrote:
> >
> > > I already came back the old conditions and I continue slow in the same
> > > way!
> >
> > OK, we need some things from you to help troubleshoot this problem.
> >
> > Postgresql version
> > schema of your tables
> > output of "explain analyze your query here"
> > a chicken foot (haha, just kidding. :-)
> >
> >
>
>
>
>


Re: slow database

From
Mark Kirkwood
Date:
If things are still slow after you have checked your keys as indicated,
then pick one query and post the output from EXPLAIN ANALYZE for the
list to examine.

oh - and ensure you are *not* still using your original postgresql.conf :-)

best wishes

Mark

scott.marlowe wrote:

>First thing I would check is to make sure all those foreign keys are the
>same type.
>
>Second, make sure you've got indexes to go with them.  I.e. on a multi-key
>fk, have a multi-key index.
>
>
>
>
>