From: | Martin Foster <martin(at)ethereal-realms(dot)org> |
---|---|
To: | PostgreSQL Novice <pgsql-novice(at)postgresql(dot)org> |
Subject: | Optimizer Parameters |
Date: | 2003-07-12 02:17:34 |
Message-ID: | 3F0F6FBE.1090300@ethereal-realms.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice pgsql-performance |
Upon testing queries with EXPLAIN ANALYSE, I started to notice that the
planner would avoid using indexes when available. Instead it would
jump to sequence scans, ignoring the index and increasing overall time
it took to get results.
I have been looking up documentation and noticed that you can somewhat
force Postgres into using Indexes when available. So I changed the
following two lines in the .conf file:
enable_seqscan = false
enable_nestloop = false
This was recommended in the documentation, and to say the least things
have really changed in performance. Queries have halved the time
needed to execute even if the estimates are insanely high compared.
I also increased this value, which apparently helps when running ANALYSE
on tables:
default_statistics_target = 1000
Now how sane is it to keep those options turned off? And what side
effects can I expect from changing default_statistics_target? And any
way to have the planner quiet guessing tens of thousands of rows will be
return when there are at most hundred?
I included the EXPLAIN ALALYSE results in an attachment to maintain
formatting of the output. Thanks in advance!
Martin Foster
Creator/Designer Ethereal Realms
martin(at)ethereal-realms(dot)org
Attachment | Content-Type | Size |
---|---|---|
planner.txt | text/plain | 9.0 KB |
From | Date | Subject | |
---|---|---|---|
Next Message | Jeffrey Melloy | 2003-07-12 03:15:08 | Re: Dont allow updation for few columns in a record. |
Previous Message | Marcus Andree S. Magalhaes | 2003-07-11 22:49:28 | replacing CR/LF |
From | Date | Subject | |
---|---|---|---|
Next Message | Nikolaus Dilger | 2003-07-12 18:25:14 | Re: Dual Xeon + HW RAID question |
Previous Message | Scott Cain | 2003-07-11 18:23:04 | Re: force the use of a particular index |