Re: Question on Explain : Index Scan

*Why is the difference in query plan, and the total runtime. Both tables
have the same btree index*

test=# explain analyze select * from test_seqindex1 where sid='AA023';
QUERY
PLAN
-------------------------------------------------------------------------------------------------------------------------------------
Index Scan using test_seqindex1_pkey on test_seqindex1 (cost=0.00..8.27
rows=1 width=28) (actual time=0.017..0.018 rows=1 loops=1)
Index Cond: ((sid)::text = 'AA023'::text)
Total runtime: 0.035 ms
(3 rows)

test=# explain analyze select * from test_seqindex2 where sid='AA023';
QUERY
PLAN
----------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on test_seqindex2 (cost=4.95..275.53 rows=73 width=30)
(actual time=57.833..71.577 rows=2 loops=1)
Recheck Cond: ((sid)::text = 'AA023'::text)
-> Bitmap Index Scan on idx_test_seqindex2_sid (cost=0.00..4.93 rows=73
width=0) (actual time=34.374..34.374 rows=2 loops=1)
Index Cond: ((sid)::text = 'AA023'::text)
Total runtime: 71.599 ms
(5 rows)

test=# \d test_seqindex1
Table "public.test_seqindex1"
Column | Type | Modifiers
--------+-----------------------+-----------
sid | character varying(13) | not null
name | character varying(80) |
Indexes:
"test_seqindex1_pkey" PRIMARY KEY, btree (sid)

test=# \d test_seqindex2
Table "public.test_seqindex2"
Column | Type | Modifiers
--------+-----------------------+-----------
eid | integer | not null
sid | character varying(13) |
ename | character varying(80) |
Indexes:
"test_seqindex2_pkey" PRIMARY KEY, btree (eid)
"idx_test_seqindex2_sid" btree (sid)

===============================================================================================

On Thu, Oct 21, 2010 at 11:09 AM, DM <dm(dot)aeqa(at)gmail(dot)com> wrote:

> perfecto, thank you for the explanation.
>
> - Deepak
>
>
> On Thu, Oct 21, 2010 at 3:20 AM, Mathieu De Zutter <mathieu(at)dezutter(dot)org>wrote:
>
>> On Thu, Oct 21, 2010 at 3:47 AM, DM <dm(dot)aeqa(at)gmail(dot)com> wrote:
>> > I was hoping the optimizer would do a join using index scan.
>> >
>> > Could some one please explain me why its not doing an index scan rather
>> than
>> > sequential scan .
>>
>>
>> A index scan would be probably slower here because you're asking for a
>> lot of rows. A lot of rows means a lot of I/O, and an index scan is
>> more I/O intensive (since it has to read the index too). If you limit
>> the result (by being more selective in your where clause, just like
>> you do in the first two queries), postgres will most likely switch to
>> index scan.
>>
>> You can see for yourself if index-scan would be faster in your case by
>> running the following command before "explain (analyze)":
>>
>> set enable_seqscan = off;
>>
>> BTW, try to use explain analyze instead of explain, that way you'll
>> see the actual timings too instead of just the planner estimates.
>>
>> Kind regards,
>> Mathieu
>>
>
>