Re: Question on Explain : Index Scan - Mailing list pgsql-general
| From | DM |
|---|---|
| Subject | Re: Question on Explain : Index Scan |
| Date | |
| Msg-id | AANLkTinm7iZuJDcXqQAojAsLEYXpvfUyRm--KEx9nNvv@mail.gmail.com Whole thread Raw |
| In response to | Re: Question on Explain : Index Scan (DM <dm.aeqa@gmail.com>) |
| Responses |
Re: Question on Explain : Index Scan
(Mathieu De Zutter <mathieu@dezutter.org>)
|
| List | pgsql-general |
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)
===============================================================================================
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.aeqa@gmail.com> wrote:
perfecto, thank you for the explanation.
- DeepakOn Thu, Oct 21, 2010 at 3:20 AM, Mathieu De Zutter <mathieu@dezutter.org> wrote:On Thu, Oct 21, 2010 at 3:47 AM, DM <dm.aeqa@gmail.com> wrote:A index scan would be probably slower here because you're asking for a
> 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 .
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
pgsql-general by date: