Skip site navigation (1) Skip section navigation (2)

Re: Simple Join

From: Mitchell Skinner <mitch(at)arctur(dot)us>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Kevin Brown <blargity(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Simple Join
Date: 2005-12-15 06:52:47
Message-ID: 1134629567.14248.27.camel@firebolt (view raw or flat)
Thread:
Lists: pgsql-performance
On Wed, 2005-12-14 at 17:47 -0500, Tom Lane wrote:
> That plan looks perfectly fine to me.  You could try forcing some other
> choices by fooling with the planner enable switches (eg set
> enable_seqscan = off) but I doubt you'll find much improvement.  There
> are too many rows being pulled from ordered_products to make an index
> nestloop a good idea.

Well, I'm no expert either, but if there was an index on
ordered_products (paid, suspended_sub, id) it should be mergejoinable
with the index on to_ship.ordered_product_id, right?  Given the
conditions on paid and suspended_sub.

If you (Kevin) try adding such an index, ideally it would get used given
that you're only pulling out a small fraction of the rows in to_ship.
If it doesn't get used, then I had a similar issue with 8.0.3 where an
index that was mergejoinable (only because of the restrictions in the
where clause) wasn't getting picked up.

Mitch

Kevin Brown wrote:
> CREATE TABLE to_ship
> (
>   id int8 NOT NULL DEFAULT nextval(('to_ship_seq'::text)::regclass),
>   ordered_product_id int8 NOT NULL,
>   bounced int4 NOT NULL DEFAULT 0,
>   operator_id varchar(20) NOT NULL,
>   "timestamp" timestamptz NOT NULL DEFAULT ('now'::text)::timestamp(6)
> with 
> time zone,
>   CONSTRAINT to_ship_pkey PRIMARY KEY (id),
>   CONSTRAINT to_ship_ordered_product_id_fkey FOREIGN KEY
> (ordered_product_id) 
> REFERENCES ordered_products (id) ON UPDATE RESTRICT ON DELETE RESTRICT
> ) 
> WITHOUT OIDS;
> 
> CREATE TABLE ordered_products
> (
>   id int8 NOT NULL DEFAULT
> nextval(('ordered_products_seq'::text)::regclass),
>   order_id int8 NOT NULL,
>   product_id int8 NOT NULL,
>   recipient_address_id int8 NOT NULL,
>   hide bool NOT NULL DEFAULT false,
>   renewal bool NOT NULL DEFAULT false,
>   "timestamp" timestamptz NOT NULL DEFAULT ('now'::text)::timestamp(6)
> with 
> time zone,
>   operator_id varchar(20) NOT NULL,
>   suspended_sub bool NOT NULL DEFAULT false,
>   quantity int4 NOT NULL DEFAULT 1,
>   price_paid numeric NOT NULL,
>   tax_paid numeric NOT NULL DEFAULT 0,
>   shipping_paid numeric NOT NULL DEFAULT 0,
>   remaining_issue_obligation int4 NOT NULL DEFAULT 0,
>   parent_product_id int8,
>   delivery_method_id int8 NOT NULL,
>   paid bool NOT NULL DEFAULT false,
>   CONSTRAINT ordered_products_pkey PRIMARY KEY (id),
>   CONSTRAINT ordered_products_order_id_fkey FOREIGN KEY (order_id)
> REFERENCES 
> orders (id) ON UPDATE RESTRICT ON DELETE RESTRICT,
>   CONSTRAINT ordered_products_parent_product_id_fkey FOREIGN KEY 
> (parent_product_id) REFERENCES ordered_products (id) ON UPDATE
> RESTRICT ON 
> DELETE RESTRICT,
>   CONSTRAINT ordered_products_recipient_address_id_fkey FOREIGN KEY 
> (recipient_address_id) REFERENCES addresses (id) ON UPDATE RESTRICT ON
> DELETE 
> RESTRICT
> ) 
> WITHOUT OIDS;
> 
> === The two indexes that should matter ===
> CREATE INDEX ordered_product_id_index
>   ON to_ship
>   USING btree
>   (ordered_product_id);
> 
> CREATE INDEX paid_index
>   ON ordered_products
>   USING btree
>   (paid);
> 
> ordered_products.id is a primary key, so it should have an implicit
> index.



In response to

Responses

pgsql-performance by date

Next:From: Johannes BühlerDate: 2005-12-15 07:07:14
Subject: effizient query with jdbc
Previous:From: BühlerDate: 2005-12-15 06:22:47
Subject: effizient query with jdbc

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group