pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSimple Join
19+ messages / 9 participants
[nested] [flat]
* Simple Join
@ 2005-12-14 22:03 Kevin Brown <blargity@gmail.com>
2005-12-14 22:47 ` Re: Simple Join Tom Lane <tgl@sss.pgh.pa.us>
2005-12-14 23:23 ` Re: Simple Join Jaime Casanova <systemguards@gmail.com>
2005-12-14 23:30 ` Re: Simple Join Mark Kirkwood <markir@paradise.net.nz>
2005-12-14 23:47 ` Re: Simple Join Steinar H. Gunderson <sgunderson@bigfoot.com>
0 siblings, 4 replies; 19+ messages in thread
From: Kevin Brown @ 2005-12-14 22:03 UTC (permalink / raw)
To: pgsql-performance
I'll just start by warning that I'm new-ish to postgresql.
I'm running 8.1 installed from source on a Debian Sarge server. I have a
simple query that I believe I've placed the indexes correctly for, and I
still end up with a seq scan. It makes sense, kinda, but it should be able
to use the index to gather the right values. I do have a production set of
data inserted into the tables, so this is running realistically:
dli=# explain analyze SELECT ordered_products.product_id
dli-# FROM to_ship, ordered_products
dli-# WHERE to_ship.ordered_product_id = ordered_products.id AND
dli-# ordered_products.paid = TRUE AND
dli-# ordered_products.suspended_sub = FALSE;
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=5126.19..31528.40 rows=20591 width=8) (actual
time=6517.438..25123.115 rows=14367 loops=1)
Hash Cond: ("outer".ordered_product_id = "inner".id)
-> Seq Scan on to_ship (cost=0.00..11529.12 rows=611612 width=8) (actual
time=393.206..15711.715 rows=611612 loops=1)
-> Hash (cost=4954.79..4954.79 rows=21759 width=16) (actual
time=6076.153..6076.153 rows=18042 loops=1)
-> Index Scan using paid_index on ordered_products
(cost=0.00..4954.79 rows=21759 width=16) (actual time=136.472..5966.275
rows=18042 loops=1)
Index Cond: (paid = true)
Filter: (paid AND (NOT suspended_sub))
Total runtime: 25136.190 ms
(8 rows)
This is running on just about the world's slowest server (with a laptop hard
drive to boot), but how can I avoid the seq scan, or in general speed up this
query?
to_ship will have far less tuples than ordered_products, but it's still not
small, as you can see.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
@ 2005-12-14 22:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-14 23:12 ` Re: Simple Join Kevin Brown <blargity@gmail.com>
2005-12-15 06:52 ` Re: Simple Join Mitchell Skinner <mitch@arctur.us>
2005-12-17 14:56 ` Re: Simple Join Bruce Momjian <pgman@candle.pha.pa.us>
3 siblings, 3 replies; 19+ messages in thread
From: Tom Lane @ 2005-12-14 22:47 UTC (permalink / raw)
To: Kevin Brown <blargity@gmail.com>; +Cc: pgsql-performance
Kevin Brown <blargity@gmail.com> writes:
> I'm running 8.1 installed from source on a Debian Sarge server. I have a
> simple query that I believe I've placed the indexes correctly for, and I
> still end up with a seq scan. It makes sense, kinda, but it should be able
> to use the index to gather the right values.
I continue to marvel at how many people think that if it's not using an
index it must ipso facto be a bad plan ...
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.
regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
2005-12-14 22:47 ` Re: Simple Join Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-12-14 23:12 ` Kevin Brown <blargity@gmail.com>
2 siblings, 0 replies; 19+ messages in thread
From: Kevin Brown @ 2005-12-14 23:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Wednesday 14 December 2005 16:47, you wrote:
> Kevin Brown <blargity@gmail.com> writes:
> > I'm running 8.1 installed from source on a Debian Sarge server. I have a
> > simple query that I believe I've placed the indexes correctly for, and I
> > still end up with a seq scan. It makes sense, kinda, but it should be
> > able to use the index to gather the right values.
>
> I continue to marvel at how many people think that if it's not using an
> index it must ipso facto be a bad plan ...
>
> 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.
That's fine, so being a postgres novice, as I stated in my original post, what
would be the best way to improve performance? Redundant column that's
updated via a trigger? I'm asking this list because I'd like to do it right,
as opposed to get it done.
> regards, tom lane
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
2005-12-14 22:47 ` Re: Simple Join Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-12-15 06:52 ` Mitchell Skinner <mitch@arctur.us>
2005-12-15 07:48 ` Re: Simple Join Kevin Brown <blargity@gmail.com>
2 siblings, 1 reply; 19+ messages in thread
From: Mitchell Skinner @ 2005-12-15 06:52 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kevin Brown <blargity@gmail.com>; 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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
2005-12-14 22:47 ` Re: Simple Join Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 06:52 ` Re: Simple Join Mitchell Skinner <mitch@arctur.us>
@ 2005-12-15 07:48 ` Kevin Brown <blargity@gmail.com>
2005-12-15 11:02 ` Re: Simple Join Mitch Skinner <lists@arctur.us>
0 siblings, 1 reply; 19+ messages in thread
From: Kevin Brown @ 2005-12-15 07:48 UTC (permalink / raw)
To: Mitchell Skinner <mitch@arctur.us>; +Cc: pgsql-performance
On Thursday 15 December 2005 00:52, you wrote:
> 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.
The following is already there:
CREATE INDEX ordered_product_id_index
ON to_ship
USING btree
(ordered_product_id);
That's why I emailed this list.
> 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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
2005-12-14 22:47 ` Re: Simple Join Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 06:52 ` Re: Simple Join Mitchell Skinner <mitch@arctur.us>
2005-12-15 07:48 ` Re: Simple Join Kevin Brown <blargity@gmail.com>
@ 2005-12-15 11:02 ` Mitch Skinner <lists@arctur.us>
2005-12-15 19:29 ` Re: Simple Join Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 1 reply; 19+ messages in thread
From: Mitch Skinner @ 2005-12-15 11:02 UTC (permalink / raw)
To: Kevin Brown <blargity@gmail.com>; +Cc: pgsql-performance
On Thu, 2005-12-15 at 01:48 -0600, Kevin Brown wrote:
> > 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.
> >
> The following is already there:
>
> CREATE INDEX ordered_product_id_index
> ON to_ship
> USING btree
> (ordered_product_id);
>
> That's why I emailed this list.
I saw that; what I'm suggesting is that that you try creating a 3-column
index on ordered_products using the paid, suspended_sub, and id columns.
In that order, I think, although you could also try the reverse. It may
or may not help, but it's worth a shot--the fact that all of those
columns are used together in the query suggests that you might do better
with a three-column index on those.
With all three columns indexed individually, you're apparently not
getting the bitmap plan that Mark is hoping for. I imagine this has to
do with the lack of multi-column statistics in postgres, though you
could also try raising the statistics target on the columns of interest.
Setting enable_seqscan to off, as others have suggested, is also a
worthwhile experiment, just to see what you get.
Mitch
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
2005-12-14 22:47 ` Re: Simple Join Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 06:52 ` Re: Simple Join Mitchell Skinner <mitch@arctur.us>
2005-12-15 07:48 ` Re: Simple Join Kevin Brown <blargity@gmail.com>
2005-12-15 11:02 ` Re: Simple Join Mitch Skinner <lists@arctur.us>
@ 2005-12-15 19:29 ` Mark Kirkwood <markir@paradise.net.nz>
2005-12-16 04:05 ` Re: Simple Join David Lang <dlang@invendra.net>
0 siblings, 1 reply; 19+ messages in thread
From: Mark Kirkwood @ 2005-12-15 19:29 UTC (permalink / raw)
To: Mitch Skinner <lists@arctur.us>; +Cc: Kevin Brown <blargity@gmail.com>; pgsql-performance
Mitch Skinner wrote:
> I saw that; what I'm suggesting is that that you try creating a 3-column
> index on ordered_products using the paid, suspended_sub, and id columns.
> In that order, I think, although you could also try the reverse. It may
> or may not help, but it's worth a shot--the fact that all of those
> columns are used together in the query suggests that you might do better
> with a three-column index on those.
>
> With all three columns indexed individually, you're apparently not
> getting the bitmap plan that Mark is hoping for. I imagine this has to
> do with the lack of multi-column statistics in postgres, though you
> could also try raising the statistics target on the columns of interest.
>
> Setting enable_seqscan to off, as others have suggested, is also a
> worthwhile experiment, just to see what you get.
>
>
Right on. Some of these "coerced" plans may perform much better. If so,
we can look at tweaking your runtime config: e.g.
effective_cache_size
random_page_cost
default_statistics_target
to see if said plans can be chosen "naturally".
cheers
Mark
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
2005-12-14 22:47 ` Re: Simple Join Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 06:52 ` Re: Simple Join Mitchell Skinner <mitch@arctur.us>
2005-12-15 07:48 ` Re: Simple Join Kevin Brown <blargity@gmail.com>
2005-12-15 11:02 ` Re: Simple Join Mitch Skinner <lists@arctur.us>
2005-12-15 19:29 ` Re: Simple Join Mark Kirkwood <markir@paradise.net.nz>
@ 2005-12-16 04:05 ` David Lang <dlang@invendra.net>
2005-12-16 21:11 ` Re: Simple Join Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 1 reply; 19+ messages in thread
From: David Lang @ 2005-12-16 04:05 UTC (permalink / raw)
To: Mark Kirkwood <markir@paradise.net.nz>; +Cc: Mitch Skinner <lists@arctur.us>; Kevin Brown <blargity@gmail.com>; pgsql-performance
On Fri, 16 Dec 2005, Mark Kirkwood wrote:
>
> Right on. Some of these "coerced" plans may perform much better. If so, we
> can look at tweaking your runtime config: e.g.
>
> effective_cache_size
> random_page_cost
> default_statistics_target
>
> to see if said plans can be chosen "naturally".
Mark, I've seen these config options listed as tweaking targets fairly
frequently, has anyone put any thought or effort into creating a test
program that could analyse the actual system and set the defaults based on
the measured performance?
David Lang
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
2005-12-14 22:47 ` Re: Simple Join Tom Lane <tgl@sss.pgh.pa.us>
2005-12-15 06:52 ` Re: Simple Join Mitchell Skinner <mitch@arctur.us>
2005-12-15 07:48 ` Re: Simple Join Kevin Brown <blargity@gmail.com>
2005-12-15 11:02 ` Re: Simple Join Mitch Skinner <lists@arctur.us>
2005-12-15 19:29 ` Re: Simple Join Mark Kirkwood <markir@paradise.net.nz>
2005-12-16 04:05 ` Re: Simple Join David Lang <dlang@invendra.net>
@ 2005-12-16 21:11 ` Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 0 replies; 19+ messages in thread
From: Mark Kirkwood @ 2005-12-16 21:11 UTC (permalink / raw)
To: David Lang <dlang@invendra.net>; +Cc: Mitch Skinner <lists@arctur.us>; Kevin Brown <blargity@gmail.com>; pgsql-performance
David Lang wrote:
> On Fri, 16 Dec 2005, Mark Kirkwood wrote:
>
>>
>> Right on. Some of these "coerced" plans may perform much better. If
>> so, we can look at tweaking your runtime config: e.g.
>>
>> effective_cache_size
>> random_page_cost
>> default_statistics_target
>>
>> to see if said plans can be chosen "naturally".
>
>
> Mark, I've seen these config options listed as tweaking targets fairly
> frequently, has anyone put any thought or effort into creating a test
> program that could analyse the actual system and set the defaults based
> on the measured performance?
>
I am sure this has been discussed before, I found this thread -
http://archives.postgresql.org/pgsql-performance/2004-07/msg00189.php
but I seem to recall others (but offhand can't find any of them).
I think that the real difficultly here is that the construction of the
test program is non trivial - for instance, the best test program for
tuning *my* workload is my application with its collection of data, but
it is probably not a good test program for *anyone else's* workload.
cheers
Mark
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
2005-12-14 22:47 ` Re: Simple Join Tom Lane <tgl@sss.pgh.pa.us>
@ 2005-12-17 14:56 ` Bruce Momjian <pgman@candle.pha.pa.us>
2 siblings, 0 replies; 19+ messages in thread
From: Bruce Momjian @ 2005-12-17 14:56 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Kevin Brown <blargity@gmail.com>; pgsql-performance
Tom Lane wrote:
> Kevin Brown <blargity@gmail.com> writes:
> > I'm running 8.1 installed from source on a Debian Sarge server. I have a
> > simple query that I believe I've placed the indexes correctly for, and I
> > still end up with a seq scan. It makes sense, kinda, but it should be able
> > to use the index to gather the right values.
>
> I continue to marvel at how many people think that if it's not using an
> index it must ipso facto be a bad plan ...
>
> 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.
We do have an FAQ item:
4.6) Why are my queries slow? Why don't they use my indexes?
--
Bruce Momjian | http://candle.pha.pa.us
pgman@candle.pha.pa.us | (610) 359-1001
+ If your life is a hard drive, | 13 Roberts Road
+ Christ can be your backup. | Newtown Square, Pennsylvania 19073
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
@ 2005-12-14 23:23 ` Jaime Casanova <systemguards@gmail.com>
2005-12-14 23:44 ` Re: Simple Join Kevin Brown <blargity@gmail.com>
3 siblings, 1 reply; 19+ messages in thread
From: Jaime Casanova @ 2005-12-14 23:23 UTC (permalink / raw)
To: Kevin Brown <blargity@gmail.com>; +Cc: pgsql-performance
On 12/14/05, Kevin Brown <blargity@gmail.com> wrote:
> I'll just start by warning that I'm new-ish to postgresql.
>
> I'm running 8.1 installed from source on a Debian Sarge server. I have a
> simple query that I believe I've placed the indexes correctly for, and I
> still end up with a seq scan. It makes sense, kinda, but it should be able
> to use the index to gather the right values. I do have a production set of
> data inserted into the tables, so this is running realistically:
>
what hardware?
> dli=# explain analyze SELECT ordered_products.product_id
> dli-# FROM to_ship, ordered_products
> dli-# WHERE to_ship.ordered_product_id = ordered_products.id AND
> dli-# ordered_products.paid = TRUE AND
> dli-# ordered_products.suspended_sub = FALSE;
> QUERY PLAN
> ----------------------------------------------------------------------------------------------------------------------------------------------------------
> Hash Join (cost=5126.19..31528.40 rows=20591 width=8) (actual
> time=6517.438..25123.115 rows=14367 loops=1)
> Hash Cond: ("outer".ordered_product_id = "inner".id)
> -> Seq Scan on to_ship (cost=0.00..11529.12 rows=611612 width=8) (actual
> time=393.206..15711.715 rows=611612 loops=1)
> -> Hash (cost=4954.79..4954.79 rows=21759 width=16) (actual
> time=6076.153..6076.153 rows=18042 loops=1)
> -> Index Scan using paid_index on ordered_products
> (cost=0.00..4954.79 rows=21759 width=16) (actual time=136.472..5966.275
> rows=18042 loops=1)
> Index Cond: (paid = true)
> Filter: (paid AND (NOT suspended_sub))
> Total runtime: 25136.190 ms
> (8 rows)
>
show the tables and the indexes for those tables
> This is running on just about the world's slowest server (with a laptop hard
> drive to boot), but how can I avoid the seq scan, or in general speed up this
> query?
>
> to_ship will have far less tuples than ordered_products, but it's still not
> small, as you can see.
>
--
regards,
Jaime Casanova
(DBA: DataBase Aniquilator ;)
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
2005-12-14 23:23 ` Re: Simple Join Jaime Casanova <systemguards@gmail.com>
@ 2005-12-14 23:44 ` Kevin Brown <blargity@gmail.com>
0 siblings, 0 replies; 19+ messages in thread
From: Kevin Brown @ 2005-12-14 23:44 UTC (permalink / raw)
To: Jaime Casanova <systemguards@gmail.com>; +Cc: pgsql-performance
On Wednesday 14 December 2005 17:23, you wrote:
> what hardware?
Via 800 mhz (about equiv to a 300 mhz pentium 2)
128 mb of slow ram
4200 rpm ide hard drive.
Told you it was slow. :-)
This is not the production system. I don't expect this to be "fast" but
everything else happens in under 2 seconds, so I know I could do this faster.
Especially becaue the information I'm looking for probably just needs some
denormalization, or other such trick to pop right out. I'm using this system
so I can locate my performance bottlenecks easier, and actually, it's plenty
fast enough except for this one single query. I don't necessarily want to
optimize the query, more than just get the info faster, so that's why I'm
posting here.
> show the tables and the indexes for those tables
No prob:
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.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
@ 2005-12-14 23:30 ` Mark Kirkwood <markir@paradise.net.nz>
2005-12-14 23:52 ` Re: Simple Join Kevin Brown <blargity@gmail.com>
2005-12-21 06:55 ` Re: Simple Join Mark Kirkwood <markir@paradise.net.nz>
3 siblings, 2 replies; 19+ messages in thread
From: Mark Kirkwood @ 2005-12-14 23:30 UTC (permalink / raw)
To: Kevin Brown <blargity@gmail.com>; +Cc: pgsql-performance
Kevin Brown wrote:
> I'll just start by warning that I'm new-ish to postgresql.
>
> I'm running 8.1 installed from source on a Debian Sarge server. I have a
> simple query that I believe I've placed the indexes correctly for, and I
> still end up with a seq scan. It makes sense, kinda, but it should be able
> to use the index to gather the right values. I do have a production set of
> data inserted into the tables, so this is running realistically:
>
> dli=# explain analyze SELECT ordered_products.product_id
> dli-# FROM to_ship, ordered_products
> dli-# WHERE to_ship.ordered_product_id = ordered_products.id AND
> dli-# ordered_products.paid = TRUE AND
> dli-# ordered_products.suspended_sub = FALSE;
You scan 600000 rows from to_ship to get about 25000 - so some way to
cut this down would help.
Try out an explicit INNER JOIN which includes the filter info for paid
and suspended_sub in the join condition (you may need indexes on each of
id, paid and suspended_sub, so that the 8.1 optimizer can use a bitmap
scan):
SELECT ordered_products.product_id
FROM to_ship INNER JOIN ordered_products
ON (to_ship.ordered_product_id = ordered_products.id
AND ordered_products.paid = TRUE AND
ordered_products.suspended_sub = FALSE);
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
2005-12-14 23:30 ` Re: Simple Join Mark Kirkwood <markir@paradise.net.nz>
@ 2005-12-14 23:52 ` Kevin Brown <blargity@gmail.com>
2005-12-15 00:36 ` Re: Simple Join Mark Kirkwood <markir@paradise.net.nz>
1 sibling, 1 reply; 19+ messages in thread
From: Kevin Brown @ 2005-12-14 23:52 UTC (permalink / raw)
To: pgsql-performance
On Wednesday 14 December 2005 17:30, Mark Kirkwood wrote:
> You scan 600000 rows from to_ship to get about 25000 - so some way to
> cut this down would help.
Yup. I'm open to anything too, as this is the only real part of the system
that cares. So either maintaining a denormalized copy column, or whatever
would be fine. We're doing far more reads than writes.
> Try out an explicit INNER JOIN which includes the filter info for paid
> and suspended_sub in the join condition (you may need indexes on each of
> id, paid and suspended_sub, so that the 8.1 optimizer can use a bitmap
> scan):
I only had two explicit indexes. One was on to_ship.ordered_product_id and
the other was on ordered_products.paid. ordered_products.id is a primary
key. This is on your query with an index added on suspended_sub:
dli=# explain analyze SELECT ordered_products.product_id
dli-# FROM to_ship INNER JOIN ordered_products
dli-# ON (to_ship.ordered_product_id = ordered_products.id
dli(# AND ordered_products.paid = TRUE AND
dli(# ordered_products.suspended_sub = FALSE);
QUERY PLAN
---------------------------------------------------------------------------------------------------------------------------------------------------------
Hash Join (cost=5126.19..31528.40 rows=20591 width=8) (actual
time=4554.190..23519.618 rows=14367 loops=1)
Hash Cond: ("outer".ordered_product_id = "inner".id)
-> Seq Scan on to_ship (cost=0.00..11529.12 rows=611612 width=8) (actual
time=11.254..15192.042 rows=611612 loops=1)
-> Hash (cost=4954.79..4954.79 rows=21759 width=16) (actual
time=4494.900..4494.900 rows=18042 loops=1)
-> Index Scan using paid_index on ordered_products
(cost=0.00..4954.79 rows=21759 width=16) (actual time=72.431..4414.697
rows=18042 loops=1)
Index Cond: (paid = true)
Filter: (paid AND (NOT suspended_sub))
Total runtime: 23532.785 ms
(8 rows)
So what's the best way to performance wiggle this info out of the db? The
list of values is only about 30 tuples long out of this query, so I was
figuring I could trigger on insert to to_ship to place the value into another
table if it didn't already exist. I'd rather the writing be slow than the
reading.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
2005-12-14 23:30 ` Re: Simple Join Mark Kirkwood <markir@paradise.net.nz>
2005-12-14 23:52 ` Re: Simple Join Kevin Brown <blargity@gmail.com>
@ 2005-12-15 00:36 ` Mark Kirkwood <markir@paradise.net.nz>
2005-12-15 07:46 ` Re: Simple Join Kevin Brown <blargity@gmail.com>
0 siblings, 1 reply; 19+ messages in thread
From: Mark Kirkwood @ 2005-12-15 00:36 UTC (permalink / raw)
To: Kevin Brown <blargity@gmail.com>; +Cc: pgsql-performance
Kevin Brown wrote:
>
>
> I only had two explicit indexes. One was on to_ship.ordered_product_id and
> the other was on ordered_products.paid. ordered_products.id is a primary
> key. This is on your query with an index added on suspended_sub:
>
> dli=# explain analyze SELECT ordered_products.product_id
> dli-# FROM to_ship INNER JOIN ordered_products
> dli-# ON (to_ship.ordered_product_id = ordered_products.id
> dli(# AND ordered_products.paid = TRUE AND
> dli(# ordered_products.suspended_sub = FALSE);
> QUERY PLAN
> ---------------------------------------------------------------------------------------------------------------------------------------------------------
> Hash Join (cost=5126.19..31528.40 rows=20591 width=8) (actual
> time=4554.190..23519.618 rows=14367 loops=1)
> Hash Cond: ("outer".ordered_product_id = "inner".id)
> -> Seq Scan on to_ship (cost=0.00..11529.12 rows=611612 width=8) (actual
> time=11.254..15192.042 rows=611612 loops=1)
> -> Hash (cost=4954.79..4954.79 rows=21759 width=16) (actual
> time=4494.900..4494.900 rows=18042 loops=1)
> -> Index Scan using paid_index on ordered_products
> (cost=0.00..4954.79 rows=21759 width=16) (actual time=72.431..4414.697
> rows=18042 loops=1)
> Index Cond: (paid = true)
> Filter: (paid AND (NOT suspended_sub))
> Total runtime: 23532.785 ms
> (8 rows)
>
Well - that had no effect at all :-) You don't have and index on
to_ship.ordered_product_id do you? - try adding one (ANALYZE again), and
let use know what happens (you may want to play with SET
enable_seqscan=off as well).
And also, if you are only ever interested in paid = true and
suspended_sub = false, then you can recreate these indexes as partials -
e.g:
CREATE INDEX paid_index ON ordered_products (paid) WHERE paid = true;
CREATE INDEX suspended_sub_index ON ordered_products (suspended_sub)
WHERE suspended_sub = false;
> So what's the best way to performance wiggle this info out of the db? The
> list of values is only about 30 tuples long out of this query, so I was
> figuring I could trigger on insert to to_ship to place the value into another
> table if it didn't already exist. I'd rather the writing be slow than the
> reading.
Yeah - all sort of horrible denormalizations are possible :-), hopefully
we can get the original query to work ok, and avoid the need to add code
or triggers to you app.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
2005-12-14 23:30 ` Re: Simple Join Mark Kirkwood <markir@paradise.net.nz>
2005-12-14 23:52 ` Re: Simple Join Kevin Brown <blargity@gmail.com>
2005-12-15 00:36 ` Re: Simple Join Mark Kirkwood <markir@paradise.net.nz>
@ 2005-12-15 07:46 ` Kevin Brown <blargity@gmail.com>
2005-12-15 08:15 ` Re: Simple Join Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 1 reply; 19+ messages in thread
From: Kevin Brown @ 2005-12-15 07:46 UTC (permalink / raw)
To: Mark Kirkwood <markir@paradise.net.nz>; +Cc: pgsql-performance
On Wednesday 14 December 2005 18:36, you wrote:
> Well - that had no effect at all :-) You don't have and index on
> to_ship.ordered_product_id do you? - try adding one (ANALYZE again), and
> let use know what happens (you may want to play with SET
> enable_seqscan=off as well).
I _DO_ have an index on to_ship.ordered_product_id. It's a btree.
> And also, if you are only ever interested in paid = true and
> suspended_sub = false, then you can recreate these indexes as partials -
> e.g:
>
> CREATE INDEX paid_index ON ordered_products (paid) WHERE paid = true;
> CREATE INDEX suspended_sub_index ON ordered_products (suspended_sub)
> WHERE suspended_sub = false;
They're currently defined as individuals and I'm depending on the bitmap
indexing.
> > So what's the best way to performance wiggle this info out of the db?
> > The list of values is only about 30 tuples long out of this query, so I
> > was figuring I could trigger on insert to to_ship to place the value into
> > another table if it didn't already exist. I'd rather the writing be slow
> > than the reading.
>
> Yeah - all sort of horrible denormalizations are possible :-), hopefully
> we can get the original query to work ok, and avoid the need to add code
> or triggers to you app.
That'd be great.
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
2005-12-14 23:30 ` Re: Simple Join Mark Kirkwood <markir@paradise.net.nz>
2005-12-14 23:52 ` Re: Simple Join Kevin Brown <blargity@gmail.com>
2005-12-15 00:36 ` Re: Simple Join Mark Kirkwood <markir@paradise.net.nz>
2005-12-15 07:46 ` Re: Simple Join Kevin Brown <blargity@gmail.com>
@ 2005-12-15 08:15 ` Mark Kirkwood <markir@paradise.net.nz>
0 siblings, 0 replies; 19+ messages in thread
From: Mark Kirkwood @ 2005-12-15 08:15 UTC (permalink / raw)
To: Kevin Brown <blargity@gmail.com>; +Cc: pgsql-performance
Kevin Brown wrote:
> On Wednesday 14 December 2005 18:36, you wrote:
>
>>Well - that had no effect at all :-) You don't have and index on
>>to_ship.ordered_product_id do you? - try adding one (ANALYZE again), and
>>let use know what happens (you may want to play with SET
>>enable_seqscan=off as well).
>
>
> I _DO_ have an index on to_ship.ordered_product_id. It's a btree.
>
Sorry - read right past it!
Did you try out enable_seqscan=off? I'm interested to see if we can get
8.1 bitmap anding the three possibly useful columns together on
ordered_products and *then* doing the join to to_ship.
Cheers
Mark
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
2005-12-14 23:30 ` Re: Simple Join Mark Kirkwood <markir@paradise.net.nz>
@ 2005-12-21 06:55 ` Mark Kirkwood <markir@paradise.net.nz>
1 sibling, 0 replies; 19+ messages in thread
From: Mark Kirkwood @ 2005-12-21 06:55 UTC (permalink / raw)
To: Kevin Brown <blargity@gmail.com>; +Cc: pgsql-performance
Mark Kirkwood wrote:
> Kevin Brown wrote:
>
>> I'll just start by warning that I'm new-ish to postgresql.
>>
>> I'm running 8.1 installed from source on a Debian Sarge server. I
>> have a simple query that I believe I've placed the indexes correctly
>> for, and I still end up with a seq scan. It makes sense, kinda, but
>> it should be able to use the index to gather the right values. I do
>> have a production set of data inserted into the tables, so this is
>> running realistically:
>>
>> dli=# explain analyze SELECT ordered_products.product_id
>> dli-# FROM to_ship, ordered_products
>> dli-# WHERE to_ship.ordered_product_id = ordered_products.id AND
>> dli-# ordered_products.paid = TRUE AND
>> dli-# ordered_products.suspended_sub = FALSE;
>
>
> You scan 600000 rows from to_ship to get about 25000 - so some way to
> cut this down would help.
>
> Try out an explicit INNER JOIN which includes the filter info for paid
> and suspended_sub in the join condition (you may need indexes on each of
> id, paid and suspended_sub, so that the 8.1 optimizer can use a bitmap
> scan):
>
>
> SELECT ordered_products.product_id
> FROM to_ship INNER JOIN ordered_products
> ON (to_ship.ordered_product_id = ordered_products.id
> AND ordered_products.paid = TRUE AND
> ordered_products.suspended_sub = FALSE);
It has been a quiet day today, so I took another look at this. If the
selectivity of clauses :
paid = TRUE
suspended_sub = FALSE
is fairly high, then rewriting as a subquery might help:
SELECT o.product_id
FROM ordered_products o
WHERE o.paid = TRUE
AND o.suspended_sub = FALSE
AND EXISTS (
SELECT 1
FROM to_ship s
WHERE s.ordered_product_id = o.id
);
However it depends on you not needing anything from to_ship in the
SELECT list...
Cheers
Mark
^ permalink raw reply [nested|flat] 19+ messages in thread
* Re: Simple Join
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
@ 2005-12-14 23:47 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
3 siblings, 0 replies; 19+ messages in thread
From: Steinar H. Gunderson @ 2005-12-14 23:47 UTC (permalink / raw)
To: pgsql-performance
On Wed, Dec 14, 2005 at 04:03:52PM -0600, Kevin Brown wrote:
> -> Index Scan using paid_index on ordered_products
> (cost=0.00..4954.79 rows=21759 width=16) (actual time=136.472..5966.275
> rows=18042 loops=1)
> Index Cond: (paid = true)
> Filter: (paid AND (NOT suspended_sub))
> Total runtime: 25136.190 ms
You might want to consider an index on (paid,suspended_sub), not just (paid);
it's probably not going to give you any dramatic improvements, but it could
help a bit.
/* Steinar */
--
Homepage: http://www.sesse.net/
^ permalink raw reply [nested|flat] 19+ messages in thread
end of thread, other threads:[~2005-12-21 06:55 UTC | newest]
Thread overview: 19+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-14 22:03 Simple Join Kevin Brown <blargity@gmail.com>
2005-12-14 22:47 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-14 23:12 ` Kevin Brown <blargity@gmail.com>
2005-12-15 06:52 ` Mitchell Skinner <mitch@arctur.us>
2005-12-15 07:48 ` Kevin Brown <blargity@gmail.com>
2005-12-15 11:02 ` Mitch Skinner <lists@arctur.us>
2005-12-15 19:29 ` Mark Kirkwood <markir@paradise.net.nz>
2005-12-16 04:05 ` David Lang <dlang@invendra.net>
2005-12-16 21:11 ` Mark Kirkwood <markir@paradise.net.nz>
2005-12-17 14:56 ` Bruce Momjian <pgman@candle.pha.pa.us>
2005-12-14 23:23 ` Jaime Casanova <systemguards@gmail.com>
2005-12-14 23:44 ` Kevin Brown <blargity@gmail.com>
2005-12-14 23:30 ` Mark Kirkwood <markir@paradise.net.nz>
2005-12-14 23:52 ` Kevin Brown <blargity@gmail.com>
2005-12-15 00:36 ` Mark Kirkwood <markir@paradise.net.nz>
2005-12-15 07:46 ` Kevin Brown <blargity@gmail.com>
2005-12-15 08:15 ` Mark Kirkwood <markir@paradise.net.nz>
2005-12-21 06:55 ` Mark Kirkwood <markir@paradise.net.nz>
2005-12-14 23:47 ` Steinar H. Gunderson <sgunderson@bigfoot.com>
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