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

postgres 7.4 vs 8.x redux: query plans

From

"Alex Deucher"

Date:

03 April 2007, 05:09:55

and here are the query plans referenced in my last email (apologies if
you get these twice, they didn't seem to go through the first time,
perhaps due to size?).  I cut out the longer ones.

Thanks,

Alex

postgres 7.4

EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
c11 from t1 where num like 'RT2350533%' or num like 'GH0405545%' or
num like 'KL8403192%';


               QUERY PLAN
----------------------------------------------------------------------
 Index Scan using t1_pkey, t1_pkey, t1_pkey on t1  (cost=0.00..17.93
rows=1 width=164) (actual time=0.103..0.238 rows=3 loops=1)
  Index Cond: ((((num)::text >= 'RT2350533'::character varying) AND
((num)::text < 'RT2350534'::character varying)) OR (((num)::text >=
'GH0405545'::character varying) AND ((num)::text <
'GH0405546'::character varying)) OR (((num)::text >=
'KL8403192'::character varying) AND ((num)::text <
'KL8403193'::character varying)))
  Filter: (((num)::text ~~ 'RT2350533%'::text) OR ((num)::text ~~
'GH0405545%'::text) OR ((num)::text ~~ 'KL8403192%'::text))
 Total runtime: 0.427 ms
(4 rows)


postgres 8.2

EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
c11 from t1 where num like 'RT2350533%' or num like 'GH0405545%' or
num like 'KL8403192%';
                                                         QUERY PLAN
---------------------------------------------------------------------
 Seq Scan on t1  (cost=0.00..918295.05 rows=1 width=156) (actual
time=15.674..26225.919 rows=3 loops=1)
  Filter: (((num)::text ~~ 'RT2350533%'::text) OR ((num)::text ~~
'GH0405545%'::text) OR ((num)::text ~~ 'KL8403192%'::text))
 Total runtime: 26225.975 ms
(3 rows)


posgres 7.4

EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
c11 from t1 where num in

('AB6253262','AB6145031','AB6091431','AB6286083','AB5857086','AB5649157','AB7089381','AB5557744','AB6314478','AB6505260','AB6249847','AB5832304');



          QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan using t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey,
t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey on t1
(cost=0.00..71.97 rows=12 width=164) (actual time=0.132..0.729 rows=12
loops=1)
  Index Cond: (((num)::text = 'AB6253262'::text) OR ((num)::text =
'AB6145031'::text) OR ((num)::text = 'AB6091431'::text) OR
((num)::text = 'AB6286083'::text) OR ((num)::text = 'AB5857086'::text)
OR ((num)::text = 'AB5649157'::text) OR ((num)::text =
'AB7089381'::text) OR ((num)::text = 'AB5557744'::text) OR
((num)::text = 'AB6314478'::text) OR ((num)::text = 'AB6505260'::text)
OR ((num)::text = 'AB6249847'::text) OR ((num)::text =
'AB5832304'::text))
 Total runtime: 1.019 ms
(3 rows)

postgres 8.2

EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
c11 from t1 where num in

('AB6253262','AB6145031','AB6091431','AB6286083','AB5857086','AB5649157','AB7089381','AB5557744','AB6314478','AB6505260','AB6249847','AB5832304');

                      QUERY PLAN

---------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=28.98..53.25 rows=12 width=156) (actual
time=61.442..61.486 rows=12 loops=1)
  Recheck Cond: ((num)::text = ANY

(('{AB6253262,AB6145031,AB6091431,AB6286083,AB5857086,AB5649157,AB7089381,AB5557744,AB6314478,AB6505260,AB6249847,AB5832304}'::character
varying[])::text[]))
  ->  Bitmap Index Scan on t1_pkey  (cost=0.00..28.98 rows=12
width=0) (actual time=61.429..61.429 rows=12 loops=1)
        Index Cond: ((num)::text = ANY

(('{AB6253262,AB6145031,AB6091431,AB6286083,AB5857086,AB5649157,AB7089381,AB5557744,AB6314478,AB6505260,AB6249847,AB5832304}'::character
varying[])::text[]))
 Total runtime: 61.544 ms
(5 rows)

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

From

Tom Lane

Date:

03 April 2007, 05:28:09

"Alex Deucher" <alexdeucher@gmail.com> writes:
> and here are the query plans referenced in my last email (apologies if
> you get these twice, they didn't seem to go through the first time,
> perhaps due to size?).  I cut out the longer ones.

The first case looks a whole lot like 8.2 does not think it can use an
index for LIKE, which suggests strongly that you've used the wrong
locale in the 8.2 installation (ie, not C).

The second pair of plans may look a lot different but in principle they
ought to perform pretty similarly.  I think the performance differential
may at root be that string comparison is way more expensive in the 8.2
installation, which again is possible if you went from C locale to some
other locale.

In short: check out "show lc_collate" in both installations.

            regards, tom lane

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

From

"Alex Deucher"

Date:

03 April 2007, 16:37:57

On 4/3/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Alex Deucher" <alexdeucher@gmail.com> writes:
> > and here are the query plans referenced in my last email (apologies if
> > you get these twice, they didn't seem to go through the first time,
> > perhaps due to size?).  I cut out the longer ones.
>
> The first case looks a whole lot like 8.2 does not think it can use an
> index for LIKE, which suggests strongly that you've used the wrong
> locale in the 8.2 installation (ie, not C).
>
> The second pair of plans may look a lot different but in principle they
> ought to perform pretty similarly.  I think the performance differential
> may at root be that string comparison is way more expensive in the 8.2
> installation, which again is possible if you went from C locale to some
> other locale.
>
> In short: check out "show lc_collate" in both installations.

OK, cool, the old one was C and the new one as not.  So I dumped the
DB and re-inited the DB with the locale set to C, then reloaded the
dump, but I'm still getting the same behavior.  Any ideas?

Thanks,

Alex

>
>                         regards, tom lane
>

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

From

"Alex Deucher"

Date:

03 April 2007, 19:31:01

On 4/3/07, Alex Deucher <alexdeucher@gmail.com> wrote:
> On 4/3/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "Alex Deucher" <alexdeucher@gmail.com> writes:
> > > and here are the query plans referenced in my last email (apologies if
> > > you get these twice, they didn't seem to go through the first time,
> > > perhaps due to size?).  I cut out the longer ones.
> >
> > The first case looks a whole lot like 8.2 does not think it can use an
> > index for LIKE, which suggests strongly that you've used the wrong
> > locale in the 8.2 installation (ie, not C).
> >
> > The second pair of plans may look a lot different but in principle they
> > ought to perform pretty similarly.  I think the performance differential
> > may at root be that string comparison is way more expensive in the 8.2
> > installation, which again is possible if you went from C locale to some
> > other locale.
> >
> > In short: check out "show lc_collate" in both installations.
>
> OK, cool, the old one was C and the new one as not.  So I dumped the
> DB and re-inited the DB with the locale set to C, then reloaded the
> dump, but I'm still getting the same behavior.  Any ideas?
>

show lc_collate;
 lc_collate
------------
 C
(1 row)


Here are some updated query plans.  The index is now used, but the
performance is still much slower on 8.2.

Thanks,

Alex

postgres 7.4


EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
c11 from t1 where num like 'RT2350533%' or num like 'GH0405545%' or
num like 'KL8403192%';


                QUERY PLAN

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan ABing t1_pkey, t1_pkey, t1_pkey on t1  (cost=0.00..17.93
rows=1 width=164) (actual time=11.652..12.132 rows=3 loops=1)
   Index Cond: ((((num)::text >= 'RT2350533'::character varying) AND
((num)::text < 'RT2350534'::character varying)) OR (((num)::text >=
'GH0405545'::character varying) AND ((num)::text <
'GH0405546'::character varying)) OR (((num)::text >=
'KL8403192'::character varying) AND ((num)::text <
'KL8403193'::character varying)))
   Filter: (((num)::text ~~ 'RT2350533%'::text) OR ((num)::text ~~
'GH0405545%'::text) OR ((num)::text ~~ 'KL8403192%'::text))
 Total runtime: 12.320 ms
(4 rows)


Postgres 8.2

EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
c11 from t1 where num like 'RT2350533%' or num like 'GH0405545%' or
num like 'KL8403192%';
                                                             QUERY
PLAN

-------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=24.03..28.04 rows=1 width=157) (actual
time=165.681..274.872 rows=3 loops=1)
   Recheck Cond: (((num)::text ~~ 'RT2350533%'::text) OR ((num)::text
~~ 'GH0405545%'::text) OR ((num)::text ~~ 'KL8403192%'::text))
   Filter: (((num)::text ~~ 'RT2350533%'::text) OR ((num)::text ~~
'GH0405545%'::text) OR ((num)::text ~~ 'KL8403192%'::text))
   ->  BitmapOr  (cost=24.03..24.03 rows=1 width=0) (actual
time=126.080..126.080 rows=0 loops=1)
         ->  Bitmap Index Scan on t1_pkey  (cost=0.00..8.01 rows=1
width=0) (actual time=61.805..61.805 rows=1 loops=1)
               Index Cond: (((num)::text >= 'RT2350533'::character
varying) AND ((num)::text < 'RT2350534'::character varying))
         ->  Bitmap Index Scan on t1_pkey  (cost=0.00..8.01 rows=1
width=0) (actual time=37.388..37.388 rows=1 loops=1)
               Index Cond: (((num)::text >= 'GH0405545'::character
varying) AND ((num)::text < 'GH0405546'::character varying))
         ->  Bitmap Index Scan on t1_pkey  (cost=0.00..8.01 rows=1
width=0) (actual time=26.876..26.876 rows=1 loops=1)
               Index Cond: (((num)::text >= 'KL8403192'::character
varying) AND ((num)::text < 'KL8403193'::character varying))
 Total runtime: 274.938 ms
(11 rows)


Postgres 7.4

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

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Index Scan ABing t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey,
t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey,
t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey,
t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey,
t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey,
t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey,
t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey, t1_pkey,
t1_pkey, t1_pkey, t1_pkey on t1  (cost=0.00..304.64 rows=50 width=164)
(actual time=0.118..2.267 rows=50 loops=1)
   Index Cond: (((num)::text = 'AB6698130'::text) OR ((num)::text =
'AB7076908'::text) OR ((num)::text = 'AB6499382'::text) OR
((num)::text = 'AB6438888'::text) OR ((num)::text = 'AB6385893'::text)
OR ((num)::text = 'AB6378237'::text) OR ((num)::text =
'AB7146973'::text) OR ((num)::text = 'AB7127138'::text) OR
((num)::text = 'AB7124531'::text) OR ((num)::text = 'AB7124513'::text)
OR ((num)::text = 'AB7123427'::text) OR ((num)::text =
'AB7121183'::text) OR ((num)::text = 'AB7121036'::text) OR
((num)::text = 'AB7110101'::text) OR ((num)::text = 'AB7100321'::text)
OR ((num)::text = 'AB7089845'::text) OR ((num)::text =
'AB7088750'::text) OR ((num)::text = 'AB7031384'::text) OR
((num)::text = 'AB7021188'::text) OR ((num)::text = 'AB7006144'::text)
OR ((num)::text = 'AB6988331'::text) OR ((num)::text =
'AB6973865'::text) OR ((num)::text = 'AB6966775'::text) OR
((num)::text = 'AB6935066'::text) OR ((num)::text = 'AB6931779'::text)
OR ((num)::text = 'AB6923412'::text) OR ((num)::text =
'AB6902405'::text) OR ((num)::text = 'AB6892488'::text) OR
((num)::text = 'AB6886288'::text) OR ((num)::text = 'AB6880467'::text)
OR ((num)::text = 'AB6874269'::text) OR ((num)::text =
'AB6871439'::text) OR ((num)::text = 'AB6868615'::text) OR
((num)::text = 'AB6819495'::text) OR ((num)::text = 'AB6807740'::text)
OR ((num)::text = 'AB6799138'::text) OR ((num)::text =
'AB6796038'::text) OR ((num)::text = 'AB6769347'::text) OR
((num)::text = 'AB6732987'::text) OR ((num)::text = 'AB6722076'::text)
OR ((num)::text = 'AB6718130'::text) OR ((num)::text =
'AB6717543'::text) OR ((num)::text = 'AB6714564'::text) OR
((num)::text = 'AB6701821'::text) OR ((num)::text = 'AB6667761'::text)
OR ((num)::text = 'AB6666630'::text) OR ((num)::text =
'AB6655069'::text) OR ((num)::text = 'AB6648287'::text) OR
((num)::text = 'AB6643969'::text) OR ((num)::text =
'AB6636412'::text))
 Total runtime: 3.073 ms
(3 rows)



Postgres 8.2


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=216.70..418.95 rows=50 width=157)
(actual time=203.880..417.165 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=198.188..198.188 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: 417.288 ms
(5 rows)

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

From

"Merlin Moncure"

Date:

03 April 2007, 20:13:53

On 4/3/07, Alex Deucher <alexdeucher@gmail.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?
* is effective_cache_size set properly?
* if nothing else works, try disable bitmap scan and running query.

merlin

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

From

"Alex Deucher"

Date:

03 April 2007, 20:18:42

On 4/3/07, Merlin Moncure <mmoncure@gmail.com> wrote:
> On 4/3/07, Alex Deucher <alexdeucher@gmail.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.

Alex

>
> merlin
>

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

From

"Alex Deucher"

Date:

03 April 2007, 20:34:29

On 4/3/07, Alex Deucher <alexdeucher@gmail.com> wrote:
> On 4/3/07, Merlin Moncure <mmoncure@gmail.com> wrote:
> > On 4/3/07, Alex Deucher <alexdeucher@gmail.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?

Alex

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

From

"Alex Deucher"

Date:

03 April 2007, 20:45:29

On 4/3/07, Alex Deucher <alexdeucher@gmail.com> wrote:
> On 4/3/07, Alex Deucher <alexdeucher@gmail.com> wrote:
> > On 4/3/07, Merlin Moncure <mmoncure@gmail.com> wrote:
> > > On 4/3/07, Alex Deucher <alexdeucher@gmail.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)

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

From

Tom Lane

Date:

03 April 2007, 21:21:11

"Alex Deucher" <alexdeucher@gmail.com> writes:
> 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?

The "ORed indexscans" plan style that was in 7.4 isn't there anymore;
we use bitmap OR'ing instead.  There actually are repeated indexscans
hidden under the "= ANY" indexscan condition in 8.2, it's just that
the mechanism for detecting duplicate matches is different.  AFAIK the
index access costs ought to be about the same either way, and the other
costs the same or better as what we did in 7.4.  It's clear though that
8.2 is taking some kind of big hit in the index access in your case.
There's something very strange going on here.

You do have both lc_collate and lc_ctype set to C, right?  What about
database encoding?

            regards, tom lane

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

From

"Alex Deucher"

Date:

03 April 2007, 21:38:40

On 4/3/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Alex Deucher" <alexdeucher@gmail.com> writes:
> > 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?
>
> The "ORed indexscans" plan style that was in 7.4 isn't there anymore;
> we use bitmap OR'ing instead.  There actually are repeated indexscans
> hidden under the "= ANY" indexscan condition in 8.2, it's just that
> the mechanism for detecting duplicate matches is different.  AFAIK the
> index access costs ought to be about the same either way, and the other
> costs the same or better as what we did in 7.4.  It's clear though that
> 8.2 is taking some kind of big hit in the index access in your case.
> There's something very strange going on here.
>
> You do have both lc_collate and lc_ctype set to C, right?  What about
> database encoding?

SHOW lc_collate ;
 lc_collate
------------
 C
(1 row)


SHOW lc_ctype ;
 lc_ctype
----------
 C
(1 row)

The encoding is UTF8, however I also built a SQL_ASCII version of the
DB to compare performance, but they both seem to perform about the
same.

Alex

SQL_ASCII:

EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
c11 from t1 where num in

('AB5679927','AB4974075','AB5066236','AB4598969','AB5009616','AB6409547','AB5593311','AB4975084','AB6604964','AB5637015','AB5135405','AB4501459','AB5605469','AB5603634','AB6000955','AB5718599','AB5328380','AB4846727');

                                                     QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=80.41..152.72 rows=18 width=157)
(actual time=157.210..283.140 rows=18 loops=1)
   Recheck Cond: ((num)::text = ANY

(('{AB5679927,AB4974075,AB5066236,AB4598969,AB5009616,AB6409547,AB5593311,AB4975084,AB6604964,AB5637015,AB5135405,AB4501459,AB5605469,AB5603634,AB6000955,AB5718599,AB5328380,AB4846727}'::character
varying[])::text[]))
   ->  Bitmap Index Scan on t1_pkey  (cost=0.00..80.41 rows=18
width=0) (actual time=140.419..140.419 rows=18 loops=1)
         Index Cond: ((num)::text = ANY

(('{AB5679927,AB4974075,AB5066236,AB4598969,AB5009616,AB6409547,AB5593311,AB4975084,AB6604964,AB5637015,AB5135405,AB4501459,AB5605469,AB5603634,AB6000955,AB5718599,AB5328380,AB4846727}'::character
varying[])::text[]))
 Total runtime: 283.214 ms
(5 rows)


UTF8:

EXPLAIN ANALYZE select num, c1, c2, c3, c4, c5, c6, c7, c8, c9, c10,
c11 from t1 where num in

('AB5679927','AB4974075','AB5066236','AB4598969','AB5009616','AB6409547','AB5593311','AB4975084','AB6604964','AB5637015','AB5135405','AB4501459','AB5605469','AB5603634','AB6000955','AB5718599','AB5328380','AB4846727');

                                                     QUERY PLAN

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Bitmap Heap Scan on t1  (cost=80.41..152.72 rows=18 width=159)
(actual time=126.194..126.559 rows=18 loops=1)
   Recheck Cond: ((num)::text = ANY

(('{AB5679927,AB4974075,AB5066236,AB4598969,AB5009616,AB6409547,AB5593311,AB4975084,AB6604964,AB5637015,AB5135405,AB4501459,AB5605469,AB5603634,AB6000955,AB5718599,AB5328380,AB4846727}'::character
varying[])::text[]))
   ->  Bitmap Index Scan on t1_pkey  (cost=0.00..80.41 rows=18
width=0) (actual time=126.155..126.155 rows=18 loops=1)
         Index Cond: ((num)::text = ANY

(('{AB5679927,AB4974075,AB5066236,AB4598969,AB5009616,AB6409547,AB5593311,AB4975084,AB6604964,AB5637015,AB5135405,AB4501459,AB5605469,AB5603634,AB6000955,AB5718599,AB5328380,AB4846727}'::character
varying[])::text[]))
 Total runtime: 126.661 ms
(5 rows)

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

From

"Alex Deucher"

Date:

03 April 2007, 21:43:52

On 4/3/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Alex Deucher" <alexdeucher@gmail.com> writes:
> > 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?
>
> The "ORed indexscans" plan style that was in 7.4 isn't there anymore;
> we use bitmap OR'ing instead.  There actually are repeated indexscans
> hidden under the "= ANY" indexscan condition in 8.2, it's just that
> the mechanism for detecting duplicate matches is different.  AFAIK the
> index access costs ought to be about the same either way, and the other
> costs the same or better as what we did in 7.4.  It's clear though that
> 8.2 is taking some kind of big hit in the index access in your case.
> There's something very strange going on here.
>
> You do have both lc_collate and lc_ctype set to C, right?  What about
> database encoding?
>

Also for reference, the old 7.4 DB is C for lc_collate and lc_ctype
and SQL_ASCII for encoding.

Alex

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

From

"Alex Deucher"

Date:

03 April 2007, 22:17:25

On 4/3/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> "Alex Deucher" <alexdeucher@gmail.com> writes:
> > 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?
>
> The "ORed indexscans" plan style that was in 7.4 isn't there anymore;
> we use bitmap OR'ing instead.  There actually are repeated indexscans
> hidden under the "= ANY" indexscan condition in 8.2, it's just that
> the mechanism for detecting duplicate matches is different.  AFAIK the
> index access costs ought to be about the same either way, and the other
> costs the same or better as what we did in 7.4.  It's clear though that
> 8.2 is taking some kind of big hit in the index access in your case.
> There's something very strange going on here.
>
> You do have both lc_collate and lc_ctype set to C, right?  What about
> database encoding?
>

hmmm... ok, this is weird.  performance seems to have improved
significantly after I reloaded postgres after adding some hew hosts to
the pg_hba.conf.  I'll run some more tests and let you know what
happens.

Alex

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

From

"Alex Deucher"

Date:

05 April 2007, 15:35:05

Ok, well, I dropped the DB and reloaded it and now all seems to be
fine and performing well.  I'm not sure what was going on before.
Thanks for everyone's help!

Alex

On 4/3/07, Alex Deucher <alexdeucher@gmail.com> wrote:
> On 4/3/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "Alex Deucher" <alexdeucher@gmail.com> writes:
> > > 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?
> >
> > The "ORed indexscans" plan style that was in 7.4 isn't there anymore;
> > we use bitmap OR'ing instead.  There actually are repeated indexscans
> > hidden under the "= ANY" indexscan condition in 8.2, it's just that
> > the mechanism for detecting duplicate matches is different.  AFAIK the
> > index access costs ought to be about the same either way, and the other
> > costs the same or better as what we did in 7.4.  It's clear though that
> > 8.2 is taking some kind of big hit in the index access in your case.
> > There's something very strange going on here.
> >
> > You do have both lc_collate and lc_ctype set to C, right?  What about
> > database encoding?
> >
>
> hmmm... ok, this is weird.  performance seems to have improved
> significantly after I reloaded postgres after adding some hew hosts to
> the pg_hba.conf.  I'll run some more tests and let you know what
> happens.
>
> Alex
>