Обсуждение: Query palns and tug-of-war with enable_sort

Hi Chaps,

We have a legacy application that used to have it's own sequential database backend, and to fetch data out of it's
tablescommands such as "find gt table by index" would be used.  

What we have now is a driver in the middle that constructs sql to access the data on pg8.3, typically of the form
"SELECT... FROM ... ORDER BY ... LIMIT n" and since we always have indexes that match the ORDER BY it creates I set
enable_sortto off because in some rare cases the planner would choose a slower plan. 

Reply with suitable comment about my foot-gun now if you're so inclined. But seeing as the purpose of our postgres
installationis to replace that legacy backend for this application, and seeing as all the other queries I put together
outsideof thae application still picked good plans, I really wasn't too worried about this. We've been building lots of
queriesfor over 5 months now, and this is the first time I've seen a non-ideal plan. 

Here's the query:

  SELECT DISTINCT mult_ref
  FROM creditINNER JOIN mult_ord ON mult_ord.transno = credit.transno
  WHERE (credit.show = 450000 OR credit.show = 450001)
  AND credit."date" >= '2009-02-16'
  AND credit."date" <= '2009-02-16'
  AND credit.cancelled = ' '
  ORDER BY mult_ref

With enable_sort on this is the plan it chooses:

 HashAggregate  (cost=14.72..14.73 rows=1 width=9)
   ->  Nested Loop  (cost=0.00..14.72 rows=1 width=9)
         ->  Index Scan using credit_index02 on credit  (cost=0.00..7.04 rows=1 width=9)
               Index Cond: ((date >= '2009-02-16'::date) AND (date <= '2009-02-16'::date))
               Filter: (((cancelled)::text = ' '::text) AND ((show = 450000::numeric) OR (show = 450
001::numeric)))
         ->  Index Scan using mult_ord_index02 on mult_ord  (cost=0.00..7.67 rows=1 width=17)
               Index Cond: (mult_ord.transno = credit.transno)

That's what I want, good. Now with enable_sort off this is the plan it chooses:

 Group  (cost=0.00..11149194.48 rows=1 width=9)
   ->  Nested Loop  (cost=0.00..11149194.47 rows=1 width=9)
         ->  Index Scan using mult_ord_index01 on mult_ord  (cost=0.00..442888.78 rows=9307812 width=17)
         ->  Index Scan using credit_index02 on credit  (cost=0.00..1.14 rows=1 width=9)
               Index Cond: ((credit.date >= '2009-02-16'::date) AND (credit.date <= '2009-02-16'::date) AND
(credit.transno= mult_ord.transno)) 
               Filter: (((credit.cancelled)::text = ' '::text) AND ((credit.show = 450000::numeric) OR (credit.show =
450001::numeric)))


With enable_sort off if I get rid of the distinct and swap the order by for a group by it picks a good plan, however
onceI stick the order by in there to try and sort it we go back to the plan above.  Now I know to a degree the planner
isreally just doing what I've told it to do, but is there anything else I can tweek to try and get a ballance? 

I've upped the statistics target from it's default of 10 to 100, which I think is probably a good idea anyway but it
doesn'taffect this quey plan. 

Any ideas?

My initial testing was done on 8.2 and this , are there any factors that might mean I'm better off with enable_sort on
in8.3? 

Regards
Glyn

Glyn Astill <glynastill@yahoo.co.uk> writes:
> With enable_sort on this is the plan it chooses:

>  HashAggregate  (cost=14.72..14.73 rows=1 width=9)
>    ->  Nested Loop  (cost=0.00..14.72 rows=1 width=9)
>          ->  Index Scan using credit_index02 on credit  (cost=0.00..7.04 rows=1 width=9)
>                Index Cond: ((date >= '2009-02-16'::date) AND (date <= '2009-02-16'::date))
>                Filter: (((cancelled)::text = ' '::text) AND ((show = 450000::numeric) OR (show = 450
> 001::numeric)))
>          ->  Index Scan using mult_ord_index02 on mult_ord  (cost=0.00..7.67 rows=1 width=17)
>                Index Cond: (mult_ord.transno = credit.transno)

> That's what I want, good. Now with enable_sort off this is the plan it chooses:

>  Group  (cost=0.00..11149194.48 rows=1 width=9)

That's just bizarre.  Can you put together a self-contained test case
for this?  Also, what version is it exactly?  ("8.3" is the wrong
answer.)

            regards, tom lane

> >  Group  (cost=0.00..11149194.48 rows=1 width=9)
>
> That's just bizarre.  Can you put together a
> self-contained test case
> for this?  Also, what version is it exactly?
> ("8.3" is the wrong
> answer.)
>

Thanks Tom,

It's 8.3.5, and I get the same results on all my servers (3 replicated servers and one "daily restore" server).

I'll be moving forward to 8.3.6 as soon as I get time...

I've slapped together a quick test case that gives the same results with explain even when I have no data in the tables
andhaven't analyzed them.  I'm not sure how silly I am for not putting any data in the tables for this test, however
seeingas it gave me the same explains what I did follows: 

1) Create my database and schema

# su postgres
$ /usr/local/pgsql/bin/createdb test --encoding='LATIN1'
$ exit
# psql -U postgres -d test

CREATE SCHEMA customers;
ALTER DATABASE "test" SET enable_sort TO off;

2) Create my tables

CREATE TABLE customers.credit
(
  recnum bigint NOT NULL DEFAULT nextval(('"customers"."credit_dfseq"'::text)::regclass),
  transno numeric(8) NOT NULL DEFAULT 0,
  "number" character varying(20) NOT NULL DEFAULT ' '::character varying,
  exmon character varying(2) NOT NULL DEFAULT ' '::character varying,
  exyear character varying(2) NOT NULL DEFAULT ' '::character varying,
  oldtick numeric(2) NOT NULL DEFAULT 0,
  coaches numeric(2) NOT NULL DEFAULT 0,
  "value" numeric(10,2) NOT NULL DEFAULT 0,
  postage numeric(6,2) NOT NULL DEFAULT 0,
  deposit numeric(6,2) NOT NULL DEFAULT 0,
  paid numeric(6,2) NOT NULL DEFAULT 0,
  amt_due numeric(6,2) NOT NULL DEFAULT 0,
  insur numeric(6,2) NOT NULL DEFAULT 0,
  sing_supp numeric(6,2) NOT NULL DEFAULT 0,
  date date NOT NULL DEFAULT '0001-01-01'::date,
  "time" character varying(5) NOT NULL DEFAULT ' '::character varying,
  seconds numeric(4) NOT NULL DEFAULT 0,
  due_by date NOT NULL DEFAULT '0001-01-01'::date,
  "user" character varying(10) NOT NULL DEFAULT ' '::character varying,
  "show" numeric(8) NOT NULL DEFAULT 0,
  show_name character varying(25) NOT NULL DEFAULT ' '::character varying,
  venue numeric(4) NOT NULL DEFAULT 0,
  tbook numeric(4) NOT NULL DEFAULT 0,
  printed character varying(1) NOT NULL DEFAULT ' '::character varying,
  source numeric(2) NOT NULL DEFAULT 0,
  source2 numeric(2) NOT NULL DEFAULT 0,
  tickets_all character varying(21) NOT NULL DEFAULT ' '::character varying,
  allocated_by character varying(10) NOT NULL DEFAULT ' '::character varying,
  allocated_date date NOT NULL DEFAULT '0001-01-01'::date,
  narrative character varying(30) NOT NULL DEFAULT ' '::character varying,
  title character varying(4) NOT NULL DEFAULT ' '::character varying,
  forename character varying(5) NOT NULL DEFAULT ' '::character varying,
  "name" character varying(15) NOT NULL DEFAULT ' '::character varying,
  add1 character varying(25) NOT NULL DEFAULT ' '::character varying,
  add2 character varying(25) NOT NULL DEFAULT ' '::character varying,
  add3 character varying(25) NOT NULL DEFAULT ' '::character varying,
  town character varying(15) NOT NULL DEFAULT ' '::character varying,
  postcode character varying(4) NOT NULL DEFAULT ' '::character varying,
  postcode2 character varying(4) NOT NULL DEFAULT ' '::character varying,
  county character varying(15) NOT NULL DEFAULT ' '::character varying,
  country_code character varying(2) NOT NULL DEFAULT ' '::character varying,
  phone character varying(20) NOT NULL DEFAULT ' '::character varying,
  authourisation numeric(8) NOT NULL DEFAULT 0,
  vat numeric(2,2) NOT NULL DEFAULT 0,
  ticonly numeric(2) NOT NULL DEFAULT 0,
  origin numeric(2) NOT NULL DEFAULT 0,
  price_type numeric(2) NOT NULL DEFAULT 0,
  show_date date NOT NULL DEFAULT '0001-01-01'::date,
  hole character varying(3) NOT NULL DEFAULT ' '::character varying,
  msort_code character varying(6) NOT NULL DEFAULT ' '::character varying,
  marker character varying(1) NOT NULL DEFAULT ' '::character varying,
  alloc_time numeric(4,2) NOT NULL DEFAULT 0,
  recorded_number character varying(10) NOT NULL DEFAULT ' '::character varying,
  allocated_mark character varying(1) NOT NULL DEFAULT ' '::character varying,
  tickets numeric(6) NOT NULL DEFAULT 0,
  date_posted date NOT NULL DEFAULT '0001-01-01'::date,
  cancelled character varying(1) NOT NULL DEFAULT ' '::character varying,
  date_printed date NOT NULL DEFAULT '0001-01-01'::date,
  shop_code numeric(2) NOT NULL DEFAULT 0,
  agent_code numeric(4) NOT NULL DEFAULT 0,
  pc character varying(8) NOT NULL DEFAULT ' '::character varying,
  spareasc1 character varying(20) NOT NULL DEFAULT ' '::character varying,
  spareasc2 character varying(20) NOT NULL DEFAULT ' '::character varying,
  sparenum1 numeric(10,2) NOT NULL DEFAULT 0,
  sparenum2 numeric(10,2) NOT NULL DEFAULT 0,
  sparedat1 date NOT NULL DEFAULT '0001-01-01'::date,
  sparedat2 date NOT NULL DEFAULT '0001-01-01'::date,
  CONSTRAINT credit_index01 PRIMARY KEY (number, transno, recnum)
)
WITH (OIDS=TRUE);

CREATE UNIQUE INDEX credit_index00
  ON customers.credit
  USING btree
  (recnum);

CREATE UNIQUE INDEX credit_index02
  ON customers.credit
  USING btree
  (date, transno, recnum);

CREATE UNIQUE INDEX credit_index03
  ON customers.credit
  USING btree
  (show, tbook, printed, recnum);

CREATE UNIQUE INDEX credit_index04
  ON customers.credit
  USING btree
  (show, transno, recnum);

CREATE UNIQUE INDEX credit_index05
  ON customers.credit
  USING btree
  (transno, show, recnum);

CREATE UNIQUE INDEX credit_index06
  ON customers.credit
  USING btree
  (date, source, source2, recnum);

CREATE UNIQUE INDEX credit_index07
  ON customers.credit
  USING btree
  (show, name, recnum);

CREATE UNIQUE INDEX credit_index08
  ON customers.credit
  USING btree
  (allocated_date, show, recnum);

CREATE UNIQUE INDEX credit_index09
  ON customers.credit
  USING btree
  (pc, add1, date, transno, recnum);

CREATE TABLE customers.mult_ord
(
  recnum bigint NOT NULL DEFAULT nextval(('"customers"."mult_ord_dfseq"'::text)::regclass),
  mult_ref numeric(8) NOT NULL DEFAULT 0,
  transno numeric(8) NOT NULL DEFAULT 0,
  CONSTRAINT mult_ord_index01 PRIMARY KEY (mult_ref, transno)
)
WITH (OIDS=TRUE);

CREATE UNIQUE INDEX mult_ord_index00
  ON customers.mult_ord
  USING btree
  (recnum);

CREATE UNIQUE INDEX mult_ord_index02
  ON customers.mult_ord
  USING btree
  (transno, recnum);

CREATE UNIQUE INDEX mult_ord_index03
  ON customers.mult_ord
  USING btree
  (transno, mult_ref);

3) Then run the query (for some reason I have to exit and then go back into psql after changing enable_sort):

explain SELECT DISTINCT mult_ref
  FROM credit INNER JOIN mult_ord ON mult_ord.transno = credit.transno
  WHERE (credit.show = 450000 OR credit.show = 450001)
  AND credit."date" >= '2009-02-16'
  AND credit."date" <= '2009-02-16'
  AND credit.cancelled = ' '
  ORDER BY mult_ref;

 Unique  (cost=0.00..491.14 rows=7 width=12)
   ->  Nested Loop  (cost=0.00..491.13 rows=7 width=12)
         ->  Index Scan using mult_ord_index01 on mult_ord  (cost=0.00..63.90 rows=1310 width=24)
         ->  Index Scan using credit_index05 on credit  (cost=0.00..0.31 rows=1 width=12)
               Index Cond: (credit.transno = mult_ord.transno)
               Filter: ((credit.date >= '2009-02-16'::date) AND (credit.date <= '2009-02-16'::date) AND
((credit.cancelled)::text= ' '::text) AND ((credit.show = 450000::numeric) OR (credit.show = 450001::numeric))) 

4) Put enable_sort back on and retry:

ALTER DATABASE "test" SET enable_sort TO on;

 Unique  (cost=22.89..22.93 rows=7 width=12)
   ->  Sort  (cost=22.89..22.91 rows=7 width=12)
         Sort Key: mult_ord.mult_ref
         ->  Nested Loop  (cost=4.30..22.80 rows=7 width=12)
               ->  Index Scan using credit_index06 on credit  (cost=0.00..8.28 rows=1 width=12)
                     Index Cond: ((date >= '2009-02-16'::date) AND (date <= '2009-02-16'::date))
                     Filter: (((cancelled)::text = ' '::text) AND ((show = 450000::numeric) OR (show =
450001::numeric)))
               ->  Bitmap Heap Scan on mult_ord  (cost=4.30..14.43 rows=7 width=24)
                     Recheck Cond: (mult_ord.transno = credit.transno)
                     ->  Bitmap Index Scan on mult_ord_index03  (cost=0.00..4.30 rows=7 width=0)
                           Index Cond: (mult_ord.transno = credit.transno)

Glyn Astill <glynastill@yahoo.co.uk> writes:
> I've slapped together a quick test case that gives the same results
> with explain even when I have no data in the tables and haven't
> analyzed them.

No, those aren't the same plans.  In particular what's bothering me is
the lack of any sort in the first plan you showed (the one with
HashAggregate at the top).  That shouldn't be possible because of the
ORDER BY --- a hash aggregate will deliver unsorted output so there
should be a sort step above it.

            regards, tom lane

>
> No, those aren't the same plans.  In particular
> what's bothering me is
> the lack of any sort in the first plan you showed (the one
> with
> HashAggregate at the top).  That shouldn't be possible
> because of the
> ORDER BY --- a hash aggregate will deliver unsorted output
> so there
> should be a sort step above it.

Ah, retracing my steps forget that; there's no sort because it's not the same query at all.

explain Select
   mult_ord.mult_ref
 From
   credit Inner Join
   mult_ord On mult_ord.transno = credit.transno
 Where
   (credit.show = 450000 Or
     credit.show = 450001) And
   credit."date" >= '2009-02-16' And
   credit."date" <= '2009-02-16' And
   credit.cancelled = ' ' group by mult_ref;


Lets just say it's been a long day.

Going back to my original point though, is there any way to get the planner to choose a better plan for the original
distinctquery? Or is it just a simple no because I set enable_sort to off? 

Glyn Astill <glynastill@yahoo.co.uk> writes:
> Ah, retracing my steps forget that; there's no sort because it's not the same query at all.

OK, that explains why things didn't seem to add up.

> Going back to my original point though, is there any way to get the planner to choose a better plan for the original
distinctquery? Or is it just a simple no because I set enable_sort to off? 

It's a bad combination.  Matters will be better in 8.4, but in existing
releases the *only* way that the planner knows how to implement DISTINCT
is sort-and-unique.  If you then throw in enable_sort = off, it's
reduced to finding an index that matches the required sort order and
scanning primarily on that, no matter how bad the resulting plan is.

I'd recommend using GROUP BY in preference to DISTINCT if you are going
to try to hobble along with enable_sort off.

Also, it'd be worth revisiting the question of whether you really still
need enable_sort off ... personally, I'd think that reducing
random_page_cost is a much saner way of nudging the planner in the
direction of preferring indexscans.

BTW, it might be a bit late for this, but you'd be a lot better off
performance-wise with bigint join keys instead of numeric(8,0).
Numeric is slow, and at that field width it's not buying you anything at
all.

            regards, tom lane

--- On Thu, 19/2/09, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> Also, it'd be worth revisiting the question of whether
> you really still
> need enable_sort off ... personally, I'd think that
> reducing
> random_page_cost is a much saner way of nudging the planner
> in the
> direction of preferring indexscans.
>

We have relatively quick storage and most of our data fits in ram, so I've dropped random_page_cost a little more and
atsome point I'll flick enable_sort back on and see how it goes. 

> BTW, it might be a bit late for this, but you'd be a
> lot better off
> performance-wise with bigint join keys instead of
> numeric(8,0).
> Numeric is slow, and at that field width it's not
> buying you anything at
> all.
>

This may be a little out of my control, there's a lot of things wrong with how our tables are set up and I generally
haveto swim through lots of 20+ year old code to discover how changes will affect it.  That said there's a lot of these
numeric(8,0)fields and I doubt switching them for bigint would cause any problems. 

Thanks Tom.