Re: Query on postgresql 7.4.2 not using index
От | Arnau |
---|---|
Тема | Re: Query on postgresql 7.4.2 not using index |
Дата | |
Msg-id | 444E4F5D.2070808@andromeiberica.com обсуждение исходный текст |
Ответ на | Re: Query on postgresql 7.4.2 not using index (Scott Marlowe <smarlowe@g2switchworks.com>) |
Список | pgsql-performance |
>>I have done the same tests on 8.1.0. >> >> >>espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM >>agenda_users_groups WHERE group_id = 9; >> QUERY PLAN >>---------------------------------------------------------------------------------------------------------------------------------------------- >> Bitmap Heap Scan on agenda_users_groups (cost=2722.26..30341.78 >>rows=400361 width=8) (actual time=145.533..680.839 rows=367026 loops=1) >> Recheck Cond: (group_id = 9::numeric) >> -> Bitmap Index Scan on i_agnusrsgrs_groupid (cost=0.00..2722.26 >>rows=400361 width=0) (actual time=142.958..142.958 rows=367026 loops=1) >> Index Cond: (group_id = 9::numeric) >> Total runtime: 1004.966 ms >>(5 rows) > > > How big are these individual records? I'm guessing a fairly good size, > since an index scan is winning. How I could know the size on an individual record? > > >>espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM >>agenda_users_groups WHERE group_id::int8 = 9; >> QUERY PLAN >>------------------------------------------------------------------------------------------------------------------------------- >> Seq Scan on agenda_users_groups (cost=0.00..60947.43 rows=12777 >>width=8) (actual time=457.963..2244.928 rows=367026 loops=1) >> Filter: ((group_id)::bigint = 9) >> Total runtime: 2571.496 ms >>(3 rows) > > > OK. Stop and think about what you're telling postgresql to do here. > > You're telling it to cast the field group_id to int8, then compare it to > 9. How can it cast the group_id to int8 without fetching it? That's > right, you're ensuring a seq scan. You need to put the int8 cast on the > other side of that equality comparison, like: > > where group_id = 9::int8 I just did what Chris Smith asked me to do :), here I paste the results I get when I change the cast. espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM agenda_users_groups WHERE group_id = 9::int8; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on agenda_users_groups (cost=2722.33..30343.06 rows=400379 width=8) (actual time=147.723..714.473 rows=367026 loops=1) Recheck Cond: (group_id = 9::numeric) -> Bitmap Index Scan on i_agnusrsgrs_groupid (cost=0.00..2722.33 rows=400379 width=0) (actual time=145.015..145.015 rows=367026 loops=1) Index Cond: (group_id = 9::numeric) Total runtime: 1038.537 ms (5 rows) espsm_moviltelevision=# EXPLAIN ANALYZE SELECT agenda_user_group_id FROM agenda_users_groups WHERE group_id = '9'::int8; QUERY PLAN ---------------------------------------------------------------------------------------------------------------------------------------------- Bitmap Heap Scan on agenda_users_groups (cost=2722.33..30343.06 rows=400379 width=8) (actual time=153.858..1192.838 rows=367026 loops=1) Recheck Cond: (group_id = 9::numeric) -> Bitmap Index Scan on i_agnusrsgrs_groupid (cost=0.00..2722.33 rows=400379 width=0) (actual time=151.298..151.298 rows=367026 loops=1) Index Cond: (group_id = 9::numeric) Total runtime: 1527.039 ms (5 rows) Thanks -- Arnau
В списке pgsql-performance по дате отправления: