Re: Query palns and tug-of-war with enable_sort

> > 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 and haven't analyzed them. I'm not sure how silly I am for not putting any data in the tables for this test, however seeing as 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)