Re: postgres 7.4 vs 8.x redux: query plans

On 4/3/07, Alex Deucher <alexdeucher(at)gmail(dot)com> wrote:
> On 4/3/07, Alex Deucher <alexdeucher(at)gmail(dot)com> wrote:
> > On 4/3/07, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> > > On 4/3/07, Alex Deucher <alexdeucher(at)gmail(dot)com> wrote:
> > > (('{AB6698130,AB7076908,AB6499382,AB6438888,AB6385893,AB6378237,AB7146973,AB7127138,AB7124531,AB7124513,AB7123427,AB7121183,AB7121036,AB7110101,AB7100321,AB7089845,AB7088750,AB7031384,AB7021188,AB7006144,AB6988331,AB6973865,AB6966775,AB6935066,AB6931779,AB6923412,AB6902405,AB6892488,AB6886288,AB6880467,AB6874269,AB6871439,AB6868615,AB6819495,AB6807740,AB6799138,AB6796038,AB6769347,AB6732987,AB6722076,AB6718130,AB6717543,AB6714564,AB6701821,AB6667761,AB6666630,AB6655069,AB6648287,AB6643969,AB6636412}'::character
> > > > varying[])::text[]))
> > > > -> Bitmap Index Scan on t1_pkey (cost=0.00..216.69 rows=50
> > > > width=0) (actual time=198.188..198.188 rows=50 loops=1)
> > > > Index Cond: ((num)::text = ANY
> > >
> > > bitmap scan:
> > > * did you run analyze?
> >
> > yes.
> >
> > > * is effective_cache_size set properly?
> >
> > It should be. I based it on the output of `free`. It's set to 988232.
> > The system has 8 GB of ram.
> >
> > > * if nothing else works, try disable bitmap scan and running query.
> >
> > I'll give that a try and post the results.
> >
>
> Turning off bitmapscan ends up doing a sequential scan. Turning off
> both bitmapscan and seqscan results in a bitmap heap scan. It doesn't
> seem to want to use the index at all. Any ideas?
>

Here are some new query plans:

db=# set enable_bitmapscan = 0;
SET
db=# EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9,
c10, c11 from t1 where num in
('AB7089845','AB7044044','AB6873406','AB6862832','AB6819495','AB6708597','AB6671991','AB6549872','AB6421947','AB6295753','AB6289624','AB6151788','AB5837918','AB5822713','AB5795628','AB5784823','AB5784821','AB5686690','AB5661775','AB5448834','AB5388364','AB5364097','AB5323555','AB5282594','AB5237773','AB5204489','AB5187317','AB5171933','AB4876942','AB4825258','AB4823674','AB4787291','AB4760770','AB4665795','AB4404890','AB4213700','AB4202246','AB4164081','AB4048489','AB4040744','AB4015258','AB4011789','AB3997762');

QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Seq Scan on t1 (cost=0.00..2058004.69 rows=43 width=157) (actual
time=31227.514..39911.029 rows=43 loops=1)
Filter: ((num)::text = ANY
(('{AB7089845,AB7044044,AB6873406,AB6862832,AB6819495,AB6708597,AB6671991,AB6549872,AB6421947,AB6295753,AB6289624,AB6151788,AB5837918,AB5822713,AB5795628,AB5784823,AB5784821,AB5686690,AB5661775,AB5448834,AB5388364,AB5364097,AB5323555,AB5282594,AB5237773,AB5204489,AB5187317,AB5171933,AB4876942,AB4825258,AB4823674,AB4787291,AB4760770,AB4665795,AB4404890,AB4213700,AB4202246,AB4164081,AB4048489,AB4040744,AB4015258,AB4011789,AB3997762}'::character
varying[])::text[]))
Total runtime: 39911.192 ms
(3 rows)

db=# set enable_seqscan = 0;
SET
db=# show enable_bitmapscan;
enable_bitmapscan
-------------------
off
(1 row)

db=# show enable_seqscan ;
enable_seqscan
----------------
off
(1 row)

db=# EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9,
c10, c11 from t1 where num in
('AB6698130','AB7076908','AB6499382','AB6438888','AB6385893','AB6378237','AB7146973','AB7127138','AB7124531','AB7124513','AB7123427','AB7121183','AB7121036','AB7110101','AB7100321','AB7089845','AB7088750','AB7031384','AB7021188','AB7006144','AB6988331','AB6973865','AB6966775','AB6935066','AB6931779','AB6923412','AB6902405','AB6892488','AB6886288','AB6880467','AB6874269','AB6871439','AB6868615','AB6819495','AB6807740','AB6799138','AB6796038','AB6769347','AB6732987','AB6722076','AB6718130','AB6717543','AB6714564','AB6701821','AB6667761','AB6666630','AB6655069','AB6648287','AB6643969','AB6636412');

QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Bitmap Heap Scan on t1 (cost=100000216.70..100000418.95 rows=50
width=157) (actual time=236.200..236.999 rows=50 loops=1)
Recheck Cond: ((num)::text = ANY
(('{AB6698130,AB7076908,AB6499382,AB6438888,AB6385893,AB6378237,AB7146973,AB7127138,AB7124531,AB7124513,AB7123427,AB7121183,AB7121036,AB7110101,AB7100321,AB7089845,AB7088750,AB7031384,AB7021188,AB7006144,AB6988331,AB6973865,AB6966775,AB6935066,AB6931779,AB6923412,AB6902405,AB6892488,AB6886288,AB6880467,AB6874269,AB6871439,AB6868615,AB6819495,AB6807740,AB6799138,AB6796038,AB6769347,AB6732987,AB6722076,AB6718130,AB6717543,AB6714564,AB6701821,AB6667761,AB6666630,AB6655069,AB6648287,AB6643969,AB6636412}'::character
varying[])::text[]))
-> Bitmap Index Scan on t1_pkey (cost=0.00..216.69 rows=50
width=0) (actual time=236.163..236.163 rows=50 loops=1)
Index Cond: ((num)::text = ANY
(('{AB6698130,AB7076908,AB6499382,AB6438888,AB6385893,AB6378237,AB7146973,AB7127138,AB7124531,AB7124513,AB7123427,AB7121183,AB7121036,AB7110101,AB7100321,AB7089845,AB7088750,AB7031384,AB7021188,AB7006144,AB6988331,AB6973865,AB6966775,AB6935066,AB6931779,AB6923412,AB6902405,AB6892488,AB6886288,AB6880467,AB6874269,AB6871439,AB6868615,AB6819495,AB6807740,AB6799138,AB6796038,AB6769347,AB6732987,AB6722076,AB6718130,AB6717543,AB6714564,AB6701821,AB6667761,AB6666630,AB6655069,AB6648287,AB6643969,AB6636412}'::character
varying[])::text[]))
Total runtime: 237.121 ms
(5 rows)