I assume you mean random_page_cost? It is currently set to 4.0 - is it better to increase or decrease this value?
Thank you Ogden On Sep 21, 2010, at 1:06 PM, Kenneth Marshall wrote: > You DB is more than likely cached. You should adjust your > page costs to better reflect reality and then the planner > can make more accurate estimates and then choose the proper > plan. > > Cheers, > Ken > > On Tue, Sep 21, 2010 at 12:32:01PM -0500, Ogden wrote: >> Hello, >> >> I have received some help from the IRC channel, however, the problem still >> exists. When running the following query with enable_seqscan set to 0, it >> takes less than a second, whereas with it set to 1, the query returns in 14 >> seconds. The machine itself has 8GB Ram and is running PostgreSQL 9.0 on >> Debian Lenny. The database size is about 7GB. >> >> >> Query: >> SELECT tr.id, tr.sid >> FROM >> test_registration tr, >> INNER JOIN test_registration_result r on (tr.id = >> r.test_registration_id) >> WHERE. >> >> tr.test_administration_id='32a22b12-aa21-11df-a606-96551e8f4e4c'::uuid >> GROUP BY tr.id, tr.sid >> >> >> >> demo=# \d test_registration >> Table "public.test_registration" >> Column | Type | Modifiers >> >> ------------------------+-----------------------------+------------------------ >> id | uuid | not null >> sid | character varying(36) | not null >> created_date | timestamp without time zone | not null default now() >> modified_date | timestamp without time zone | not null >> test_administration_id | uuid | not null >> teacher_number | character varying(15) | >> test_version_id | uuid | >> Indexes: >> "test_registration_pkey" PRIMARY KEY, btree (id) >> "test_registration_sid_key" UNIQUE, btree (sid, test_administration_id) >> "test_registration_teacher" btree (teacher_number) >> "test_registration_test_id" btree (test_administration_id) >> >> demo=# \d test_registration_result >> Table "public.test_registration_result" >> Column | Type | Modifiers >> ----------------------+-----------------------+----------- >> answer | character varying(15) | >> question_id | uuid | not null >> score | double precision | >> test_registration_id | uuid | not null >> Indexes: >> "test_registration_result_pkey" PRIMARY KEY, btree (question_id, >> test_registration_id) >> "test_registration_result_answer" btree (test_registration_id, answer, >> score) >> "test_registration_result_test" btree (test_registration_id) >> >> >> Explain Analyze: >> >> >> -------------------------------------------------------------------------------------------------------------------------------------------------------------------------- >> HashAggregate (cost=951169.97..951198.37 rows=2840 width=25) (actual >> time=14669.039..14669.843 rows=2972 loops=1) >> -> Hash Join (cost=2988.07..939924.85 rows=2249024 width=25) (actual >> time=551.464..14400.061 rows=638980 loops=1) >> Hash Cond: (r.test_registration_id = tr.id) >> -> Seq Scan on test_registration_result r (cost=0.00..681946.72 >> rows=37199972 width=16) (actual time=0.015..6073.101 rows=37198734 loops=1) >> -> Hash (cost=2952.57..2952.57 rows=2840 width=25) (actual >> time=2.516..2.516 rows=2972 loops=1) >> Buckets: 1024 Batches: 1 Memory Usage: 160kB >> -> Bitmap Heap Scan on test_registration tr >> (cost=44.29..2952.57 rows=2840 width=25) (actual time=0.528..1.458 rows=2972 >> loops=1) >> Recheck Cond: (test_administration_id = >> 'e26a165a-c19f-11df-be2f-778af560e5a2'::uuid) >> -> Bitmap Index Scan on >> test_registration_test_administration_id (cost=0.00..43.58 rows=2840 >> width=0) (actual time=0.507..0.507 rows=2972 loops=1) >> Index Cond: (test_administration_id = >> 'e26a165a-c19f-11df-be2f-778af560e5a2'::uuid) >> Total runtime: 14670.337 ms >> (11 rows) >> >> >> real 0m14.698s >> user 0m0.000s >> sys 0m0.008s >> >> >> With "set enable_seqscan=0;" >> >> >> SET >> >> QUERY PLAN >> >> ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- >> HashAggregate (cost=1225400.19..1225428.59 rows=2840 width=25) (actual >> time=748.397..749.160 rows=2972 loops=1) >> -> Nested Loop (cost=0.00..1214155.07 rows=2249024 width=25) (actual >> time=0.107..465.165 rows=638980 loops=1) >> -> Index Scan using test_registration_test_administration_id on >> test_registration tr (cost=0.00..4413.96 rows=2840 width=25) (actual >> time=0.050..1.610 rows=2972 loops=1) >> Index Cond: (test_administration_id = >> 'e26a165a-c19f-11df-be2f-778af560e5a2'::uuid) >> -> Index Scan using test_registration_result_answer on >> test_registration_result r (cost=0.00..416.07 rows=792 width=16) (actual >> time=0.019..0.106 rows=215 loops=2972) >> Index Cond: (r.test_registration_id = tr.id) >> Total runtime: 749.745 ms >> (7 rows) >> >> >> real 0m0.759s >> user 0m0.008s >> sys 0m0.000s >> >> >> The following parameters are changed in postgresql.conf and I have routinely >> vacuum analyzed the tables and database: >> >> shared_buffers = 2048MB >> work_mem = 8MB >> maintenance_work_mem = 256MB >> wal_buffers = 640kB >> random_page_cost = 4.0 >> effective_cache_size = 7000MB >> default_statistics_target = 200 >> >> >> free -m: >> total used free shared buffers cached >> Mem: 8003 7849 153 0 25 7555 >> -/+ buffers/cache: 268 7735 >> Swap: 7640 0 7639 >> >> >> Any help would be appreciated. Thank you very much. >> >> Ogden > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
