Query much faster with enable_seqscan=0
| От | Ogden |
|---|---|
| Тема | Query much faster with enable_seqscan=0 |
| Дата | |
| Msg-id | 0D5B9ED0-00C3-4EEB-8336-0B2F8A884620@darkstatic.com обсуждение исходный текст |
| Ответы |
Re: Query much faster with enable_seqscan=0
(Kenneth Marshall <ktm@rice.edu>)
Re: Query much faster with enable_seqscan=0 (Tom Lane <tgl@sss.pgh.pa.us>) |
| Список | pgsql-performance |
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
В списке pgsql-performance по дате отправления: