pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Alex Deucher <alexdeucher@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: postgres 7.4 vs 8.x redux: query plans
Date: Tue, 3 Apr 2007 15:30:50 -0400
Message-ID: <a728f9f90704031230y11451335i1a56fd8a7eba3a8e@mail.gmail.com> (raw)
In-Reply-To: <a728f9f90704030937t4855d730u41bd890de3d84603@mail.gmail.com>
References: <a728f9f90704022209x1cd688f6pa9c8c2584376897e@mail.gmail.com>
	<17931.1175578083@sss.pgh.pa.us>
	<a728f9f90704030937t4855d730u41bd890de3d84603@mail.gmail.com>

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)



view thread (13+ messages)  latest in thread

Message-ID: <a728f9f90704031230y11451335i1a56fd8a7eba3a8e@mail.gmail.com>
Permalink:  ../a728f9f90704031230y11451335i1a56fd8a7eba3a8e@mail.gmail.com/
Also on:    postgresql.org/message-id/a728f9f90704031230y11451335i1a56fd8a7eba3a8e@mail.gmail.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: alexdeucher@gmail.com, tgl@sss.pgh.pa.us
  Subject: Re: postgres 7.4 vs 8.x redux: query plans
  In-Reply-To: <a728f9f90704031230y11451335i1a56fd8a7eba3a8e@mail.gmail.com>
  References: <a728f9f90704022209x1cd688f6pa9c8c2584376897e@mail.gmail.com> <17931.1175578083@sss.pgh.pa.us> <a728f9f90704030937t4855d730u41bd890de3d84603@mail.gmail.com> <a728f9f90704031230y11451335i1a56fd8a7eba3a8e@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox