On Thu, 16 Apr 2009, Tom Lane wrote:
Kris Jurka <books@xxxxxxxxxx> writes:
PG (8.3.7) doesn't seem to want to do a hash join across two partitioned
tables.
Could we see the whole declaration of these tables? (pg_dump -s output
would be convenient)
The attached table definition with no data wants to mergejoin first, but
after disabling mergejoin it does indeed do a hashjoin.
Looking back at the cost estimates for the merge and nestloop joins, it
seems to be selecting the number of rows in the cartesian product * .005
while the number of output rows in this case is 2437 (cartesian product *
4e-9). Perhaps the cost estimates for the real data are so high because
of this bogus row count that the fudge factor to disable mergejoin isn't
enough?
Kris Jurka
CREATE TABLE impounds (
vin character varying(17) NOT NULL,
impounddate date NOT NULL,
eventtypeid integer NOT NULL,
ori character varying(9),
platenumber character varying(8),
platestate character varying(2),
plateyear integer,
platetype character varying(2),
vehicleyear integer,
vehiclemake text,
vehiclemodel text,
vehiclestyle text,
vehiclecolor text,
townotes text,
damagenotes text,
platenotes text,
custodynotes text,
sourcenotes text,
referencenumber1 text,
referencenumber2 text,
referencenumber3 text,
contactname text,
contactphone text
);
ALTER TABLE ONLY impounds
ADD CONSTRAINT impounds_pk PRIMARY KEY (vin, impounddate, eventtypeid);
CREATE TABLE impounds_s1 () INHERITS (impounds);
CREATE TABLE impounds_s2 () INHERITS (impounds);
CREATE TABLE impounds_s3 () INHERITS (impounds);
CREATE TABLE impounds_s4 () INHERITS (impounds);
ALTER TABLE impounds_s1 ADD CONSTRAINT impounds_s1_pk PRIMARY KEY (vin, impounddate);
ALTER TABLE impounds_s2 ADD CONSTRAINT impounds_s2_pk PRIMARY KEY (vin, impounddate);
ALTER TABLE impounds_s3 ADD CONSTRAINT impounds_s3_pk PRIMARY KEY (vin, impounddate);
ALTER TABLE impounds_s4 ADD CONSTRAINT impounds_s4_pk PRIMARY KEY (vin, impounddate);
CREATE TABLE liens (
agentid integer NOT NULL,
vin character varying(17) NOT NULL,
liendate date,
accountnumber character varying(50),
customername character varying(50),
state character varying(2),
vehiclemake character varying(20),
vehiclemodel character varying(20),
vehicleyear integer
);
ALTER TABLE ONLY liens
ADD CONSTRAINT liens_pk PRIMARY KEY (vin, agentid);
CREATE TABLE liens_s1 () INHERITS (liens);
CREATE TABLE liens_s2 () INHERITS (liens);
CREATE TABLE liens_s3 () INHERITS (liens);
CREATE TABLE liens_s4 () INHERITS (liens);
CREATE TABLE liens_s5 () INHERITS (liens);
CREATE TABLE liens_s6 () INHERITS (liens);
CREATE TABLE liens_s7 () INHERITS (liens);
ALTER TABLE liens_s1 ADD CONSTRAINT lines_s1_pk PRIMARY KEY (vin);
ALTER TABLE liens_s2 ADD CONSTRAINT lines_s2_pk PRIMARY KEY (vin);
ALTER TABLE liens_s3 ADD CONSTRAINT lines_s3_pk PRIMARY KEY (vin);
ALTER TABLE liens_s4 ADD CONSTRAINT lines_s4_pk PRIMARY KEY (vin);
ALTER TABLE liens_s5 ADD CONSTRAINT lines_s5_pk PRIMARY KEY (vin);
ALTER TABLE liens_s6 ADD CONSTRAINT lines_s6_pk PRIMARY KEY (vin);
ALTER TABLE liens_s7 ADD CONSTRAINT lines_s7_pk PRIMARY KEY (vin);
--
Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
