Re: seqscan for 100 out of 3M rows, index present

On Wed, Jun 26, 2013 at 12:18 PM, Willy-Bas Loos <willybas(at)gmail(dot)com> wrote:
> plan with enable_seqscan off:
>
> Aggregate (cost=253892.48..253892.49 rows=1 width=0) (actual
> time=208.681..208.681 rows=1 loops=1)
> -> Nested Loop (cost=5.87..253889.49 rows=1198 width=0) (actual
> time=69.403..208.647 rows=17 loops=1)
> -> Index Scan using geo_blok_idx on geo g (cost=0.00..1314.43
> rows=500 width=8) (actual time=45.776..46.147 rows=121 loops=1)
> Index Cond: (blok = 1942)
> -> Bitmap Heap Scan on bmp_data d (cost=5.87..502.91 rows=179
> width=8) (actual time=1.340..1.341 rows=0 loops=121)
> Recheck Cond: (geo_id = g.geo_id)
> -> Bitmap Index Scan on bmp_data_geo_idx (cost=0.00..5.82
> rows=179 width=0) (actual time=1.206..1.206 rows=0 loops=121)
> Index Cond: (geo_id = g.geo_id)
> Total runtime: 208.850 ms
>
> On Wed, Jun 26, 2013 at 9:08 PM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:
>> Aggregate (cost=60836.71..60836.72 rows=1 width=0) (actual
>> time=481.526..481.526 rows=1 loops=1)
>> -> Hash Join (cost=1296.42..60833.75 rows=1184 width=0) (actual
>> time=317.403..481.513 rows=17 loops=1)
>> Hash Cond: (d2.gid = g2.gid)
>> -> Seq Scan on d2 (cost=0.00..47872.54 rows=3107454 width=8)
>> (actual time=0.013..231.707 rows=3107454 loops=1)
>> -> Hash (cost=1290.24..1290.24 rows=494 width=8) (actual
>> time=0.207..0.207 rows=121 loops=1)
>> Buckets: 1024 Batches: 1 Memory Usage: 5kB
>> -> Index Scan using g_blok on g2 (cost=0.00..1290.24
>> rows=494 width=8) (actual time=0.102..0.156 rows=121 loops=1)
>> Index Cond: (k = 1942)
>> Total runtime: 481.600 ms

These are plans of two different queries. Please show the second one
(where d2, g2, etc are) with secscans off.

--
Kind regards,
Sergey Konoplev
PostgreSQL Consultant and DBA

Profile: http://www.linkedin.com/in/grayhemp
Phone: USA +1 (415) 867-9984, Russia +7 (901) 903-0499, +7 (988) 888-1979
Skype: gray-hemp
Jabber: gray(dot)ru(at)gmail(dot)com