Re: Query much faster with enable_seqscan=0

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
withenable_seqscan set to 0, it takes less than a second, whereas with it set to 1, the query returns in 14 seconds.
Themachine 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.101rows=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.458rows=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=2840width=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=792width=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