Bruno Wolff III wrote:
On Mon, Jun 13, 2005 at 15:05:00 -0400,
Madison Kelly <[EMAIL PROTECTED]> wrote:
Wow!
With the sequence scan off my query took less than 2sec. When I turned
it back on the time jumped back up to just under 14sec.
tle-bu=> set enable_seqscan = off; SET
tle-bu=> EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display
FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC,
file_parent_dir ASC, file_name ASC;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------
Index Scan using file_info_7_display_idx on file_info_7
(cost=0.00..83171.78 rows=25490 width=119) (actual
time=141.405..1700.459 rows=25795 loops=1)
Index Cond: ((file_type)::text = 'd'::text)
Total runtime: 1851.366 ms
(3 rows)
tle-bu=> set enable_seqscan = on; SET
tle-bu=> EXPLAIN ANALYZE SELECT file_name, file_parent_dir, file_display
FROM file_info_7 WHERE file_type='d' ORDER BY file_type ASC,
file_parent_dir ASC, file_name ASC;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------
Sort (cost=14810.92..14874.65 rows=25490 width=119) (actual
time=13605.185..13728.436 rows=25795 loops=1)
Sort Key: file_type, file_parent_dir, file_name
-> Seq Scan on file_info_7 (cost=0.00..11956.84 rows=25490
width=119) (actual time=0.048..2018.996 rows=25795 loops=1)
Filter: ((file_type)::text = 'd'::text)
Total runtime: 13865.830 ms
(5 rows)
So the index obiously provides a major performance boost! I just need
to figure out how to tell the planner how to use it...
The two things you probably want to look at are (in postgresql.conf):
effective_cache_size = 10000 # typically 8KB each
random_page_cost = 2 # units are one sequential page fetch cost
Increasing effective cache size and decreasing the penalty for random
disk fetches will favor using index scans. People have reported that
dropping random_page_cost from the default of 4 to 2 works well.
Effective cache size should be set to some reasonable estimate of
the memory available on your system to postgres, not counting that
set aside for shared buffers.
However, since the planner thought the index scan plan was going to be 6 times
slower than the sequential scan plan, I don't know if tweaking these values
enough to switch the plan choice won't cause problems for other queries.
Hmm,
In this case I am trying to avoid modifying 'postgres.conf' and am
trying to handle any performance tweaks within my program through SQL
calls. This is because (I hope) my program will be installed by many
users and I don't want to expect them to be able/comfortable playing
with 'postgres.conf'. I do plan later though to create a section in the
docs with extra tweaks for more advanced users and in that case I will
come back to this and try/record just that.
In the mean time Tom's recommendation works from perl by calling:
$DB->do("SET ENABLE_SEQSCAN TO OFF") || die...
<query...>
$DB->do("SET ENABLE_SEQSCAN TO ON") || die...
Forces the index to be used. It isn't clean but it works for now and
I don't need to do anything outside my program.
Lacking any other ideas, thank you very, very much for sticking with
this and helping me out!
Madison
--
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Madison Kelly (Digimer)
TLE-BU, The Linux Experience; Back Up
http://tle-bu.thelinuxexperience.com
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
