pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedNested loops are killing throughput
8+ messages / 2 participants
[nested] [flat]
* Nested loops are killing throughput
@ 2006-09-19 14:46 CG <cgg007@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: CG @ 2006-09-19 14:46 UTC (permalink / raw)
To: pgsql-sql
Postgresql 8.1
I've tried turning off nested loops to see what the query planner would choose instead of nested loops. It chose a hash join, and it still had a nested loop in the mix! How can I entice the query planner to use a simpler join scheme? What criteria is used to determine whether or not a merge join will be used?
Here's an explain analyze...
Subquery Scan foo (cost=762.21..762.50 rows=1 width=12) (actual time=10784.849..10786.992 rows=9 loops=1)
-> GroupAggregate (cost=762.21..762.24 rows=1 width=30) (actual time=10767.702..10768.539 rows=9 loops=1)
-> Sort (cost=762.21..762.21 rows=1 width=30) (actual time=10767.525..10767.989 rows=360 loops=1)
Sort Key: (dpdl.field_value)::timestamp with time zone
-> Nested Loop (cost=2.01..762.20 rows=1 width=30) (actual time=283.824..10749.007 rows=360 loops=1)
-> Nested Loop (cost=0.00..744.28 rows=1 width=16) (actual time=31.227..6980.765 rows=5436 loops=1)
-> Nested Loop (cost=0.00..135.29 rows=101 width=16) (actual time=25.514..273.660 rows=5436 loops=1)
-> Index Scan using ut_company_name_idx on user_table ut (cost=0.00..21.96 rows=5 width=11) (actual time=6.596..6.649 rows=9 loops=1)
Index Cond: ((company_name)::text = Acme, Inc.'::text)
-> Index Scan using packet_user_idx on packet dp (cost=0.00..19.89 rows=222 width=27) (actual time=16.939..28.025 rows=604 loops=9)
Index Cond: ((dp.username)::text = ("outer".username)::text)
Filter: (trans_date > (date_trunc('month'::text, (now() - '1 mon'::interval)) - '1 year 6 mons'::interval))
-> Index Scan using packet_status_puuid_pkey on packet_status dps (cost=0.00..6.02 rows=1 width=16) (actual time=1.226..1.228 rows=1 loops=5436)
Index Cond: (dps.packet_uuid = "outer".packet_uuid)
Filter: ((status & 2) = 0)
-> Bitmap Heap Scan on packet_datalink dpdl (cost=2.01..17.87 rows=4 width=30) (actual time=0.685..0.688 rows=0 loops=5436)
Recheck Cond: (dpdl.packet_uuid = "outer".packet_uuid)
Filter: (((field_name)::text = 'event_date'::text) AND ((field_value)::date >= ('now'::text)::date))
-> Bitmap Index Scan on packet_dl_puuid_idx (cost=0.00..2.01 rows=4 width=0) (actual time=0.402..0.402 rows=4 loops=5436)
Index Cond: (dpdl.packet_uuid = "outer".packet_uuid)
Total runtime: 10787.198 ms
Also, no one here can figure out why the row count prediction is off on the packet table either. It's vacuumed and analyzed.
...
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Nested loops are killing throughput
@ 2006-09-19 15:03 Tom Lane <tgl@sss.pgh.pa.us>
parent: CG <cgg007@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2006-09-19 15:03 UTC (permalink / raw)
To: CG <cgg007@yahoo.com>; +Cc: pgsql-sql
CG <cgg007@yahoo.com> writes:
> -> Nested Loop (cost=0.00..744.28 rows=1 width=16) (actual time=31.227..6980.765 rows=5436 loops=1)
> -> Nested Loop (cost=0.00..135.29 rows=101 width=16) (actual time=25.514..273.660 rows=5436 loops=1)
> ...
> -> Index Scan using packet_status_puuid_pkey on packet_status dps (cost=0.00..6.02 rows=1 width=16) (actual time=1.226..1.228 rows=1 loops=5436)
> Index Cond: (dps.packet_uuid = "outer".packet_uuid)
> Filter: ((status & 2) = 0)
One problem you've got is that the planner has no stats about the
selectivity of that status condition. My advice would be to forget the
cute bitmask and store one or more plain boolean columns. Easier to
write queries against and way more tractable statistically.
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Nested loops are killing throughput
@ 2006-09-19 18:05 CG <cgg007@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: CG @ 2006-09-19 18:05 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
----- Original Message ----
From: Tom Lane <tgl@sss.pgh.pa.us>
To: CG <cgg007@yahoo.com>
Cc: pgsql-sql@postgresql.org
Sent: Tuesday, September 19, 2006 11:03:07 AM
Subject: Re: [SQL] Nested loops are killing throughput
CG <cgg007@yahoo.com> writes:
> -> Nested Loop (cost=0.00..744.28 rows=1 width=16) (actual time=31.227..6980.765 rows=5436 loops=1)
> -> Nested Loop (cost=0.00..135.29 rows=101 width=16) (actual time=25.514..273.660 rows=5436 loops=1)
> ...
> -> Index Scan using packet_status_puuid_pkey on packet_status dps (cost=0.00..6.02 rows=1 width=16) (actual time=1.226..1.228 rows=1 loops=5436)
> Index Cond: (dps.packet_uuid = "outer".packet_uuid)
> Filter: ((status & 2) = 0)
>One problem you've got is that the planner has no stats about the
>selectivity of that status condition. My advice would be to forget the
>cute bitmask and store one or more plain boolean columns. Easier to
>write queries against and way more tractable statistically.
n/p ... I'll ditch it.
Even if I simplify that query down to a straight-forward example...
select dp.*, dps.status from dpo.packet dp, dpo.packet_status dps where dp.packet_uuid = dps.packet_uuid and dp.username='joeuser';
I'm still getting the nested loop for a join.
Nested Loop (cost=100000000.00..100013378.98 rows=2206 width=145) (actual time=46.743..18202.318 rows=2225 loops=1)
-> Index Scan using packet_user_idx on packet dp (cost=0.00..88.03 rows=2206 width=125) (actual time=42.263..124.519 rows=2225 loops=1)
Index Cond: ((username)::text = 'joeuser'::text)
-> Index Scan using packet_status_puuid_pkey on packet_status dps (cost=0.00..6.01 rows=1 width=20) (actual time=8.115..8.117 rows=1 loops=2225)
Index Cond: ("outer".packet_uuid = dps.packet_uuid)
Total runtime: 18205.880 ms
These indexes are being used, and the tables are freshly vacuum-analyzed...
CREATE UNIQUE INDEX packet_puuid_idx
ON dpo.packet
USING btree
(packet_uuid);
CREATE INDEX packet_user_idx
ON dpo.packet
USING btree
(username);
CREATE UNIQUE INDEX packet_status_puuid_pkey
ON dpo.packet_status
USING btree
(packet_uuid);
I have no idea why I'm having such a difficult time digging through this data. It should be very straight-forward.
CG
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Nested loops are killing throughput
@ 2006-09-19 18:17 Tom Lane <tgl@sss.pgh.pa.us>
parent: CG <cgg007@yahoo.com>
0 siblings, 1 reply; 8+ messages in thread
From: Tom Lane @ 2006-09-19 18:17 UTC (permalink / raw)
To: CG <cgg007@yahoo.com>; +Cc: pgsql-sql
CG <cgg007@yahoo.com> writes:
> I'm still getting the nested loop for a join.
> Nested Loop (cost=100000000.00..100013378.98 rows=2206 width=145) (actual time=46.743..18202.318 rows=2225 loops=1)
> -> Index Scan using packet_user_idx on packet dp (cost=0.00..88.03 rows=2206 width=125) (actual time=42.263..124.519 rows=2225 loops=1)
> Index Cond: ((username)::text = 'joeuser'::text)
> -> Index Scan using packet_status_puuid_pkey on packet_status dps (cost=0.00..6.01 rows=1 width=20) (actual time=8.115..8.117 rows=1 loops=2225)
> Index Cond: ("outer".packet_uuid = dps.packet_uuid)
> Total runtime: 18205.880 ms
If packet_status is large, that seems like a perfectly reasonable plan
to me. If not ... what data type is packet_uuid? Is its equality op
marked mergeable or hashable?
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Nested loops are killing throughput
@ 2006-09-19 18:59 CG <cgg007@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 8+ messages in thread
From: CG @ 2006-09-19 18:59 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
>If packet_status is large, that seems like a perfectly reasonable plan
>to me. If not ... what data type is packet_uuid? Is its equality op
>marked mergeable or hashable?
It is of type uniqueidentifier ...
CREATE OPERATOR =(
PROCEDURE = uniqueidentifier_eq,
LEFTARG = uniqueidentifier,
RIGHTARG = uniqueidentifier,
COMMUTATOR = =,
RESTRICT = eqsel,
JOIN = eqjoinsel,
HASHES,
MERGES,
SORT1 = <,
SORT2 = <,
LTCMP = <,
GTCMP = >);
It looks like it is marked both mergable and hashable.
So what size (I have no idea what units to use to think about table size... kilobytes? pages? rows?) is "large" ... It seems like we recently stepped over that imaginary line between medium and large.
We could re-tune PostgreSQL... What settings would make sense to tweak? Sort Memory?
We could partition the tables... I understand the theory behind table partitioning and partial indexes, but I've never come across a howto or even a best practices list.
We could remove data from the table... I don't like this as much, but if that's the only way, that's the only way.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Nested loops are killing throughput
@ 2006-09-19 19:15 Tom Lane <tgl@sss.pgh.pa.us>
parent: CG <cgg007@yahoo.com>
0 siblings, 2 replies; 8+ messages in thread
From: Tom Lane @ 2006-09-19 19:15 UTC (permalink / raw)
To: CG <cgg007@yahoo.com>; +Cc: pgsql-sql
CG <cgg007@yahoo.com> writes:
>> If packet_status is large, that seems like a perfectly reasonable plan
>> to me. If not ... what data type is packet_uuid? Is its equality op
>> marked mergeable or hashable?
> It is of type uniqueidentifier ...
Is that the one off gborg? It's broken because the equality function is
marked volatile, and so the planner is afraid to try to use it for
merging or hashing. (It's also not marked strict, which means you can
trivially crash the backend by passing it a null ...)
regards, tom lane
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Nested loops are killing throughput
@ 2006-09-19 20:36 CG <cgg007@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 8+ messages in thread
From: CG @ 2006-09-19 20:36 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
>Is that the one off gborg? It's broken because the equality function is
>marked volatile, and so the planner is afraid to try to use it for
>merging or hashing. (It's also not marked strict, which means you can
>trivially crash the backend by passing it a null ...)
It is the one from gborg. That's good to know.
So to fix, I should alter the supporting functions and set the ones that should not be VOLATILE to IMMUTABLE (newid() should be VOLATILE and that's about it) and set them to STRICT as well. I haven't had a problem with it crashing my backend in the three years we've been using it, but I'd rather not take any chances.
I know the hacker list has been batting back and forth the idea of a built-in UUID type. I'll be interested to see if you end up with a standard UUID type, a fixed field type with multiple generators for differing levels of paranoia, or the status quo.
CG
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Nested loops are killing throughput
@ 2006-09-20 18:10 CG <cgg007@yahoo.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 8+ messages in thread
From: CG @ 2006-09-20 18:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-sql
>Is that the one off gborg? It's broken because the equality function is
>marked volatile, and so the planner is afraid to try to use it for
>merging or hashing. (It's also not marked strict, which means you can
>trivially crash the backend by passing it a null ...)
Tom, you are a genius. I changed the supporting functions and started getting what I expected.
You were right on the money and saved my bacon.
I don't say it often enough: Thanks!
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2006-09-20 18:10 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-09-19 14:46 Nested loops are killing throughput CG <cgg007@yahoo.com>
2006-09-19 15:03 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-19 18:05 ` CG <cgg007@yahoo.com>
2006-09-19 18:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-19 18:59 ` CG <cgg007@yahoo.com>
2006-09-19 19:15 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-09-19 20:36 ` CG <cgg007@yahoo.com>
2006-09-20 18:10 ` CG <cgg007@yahoo.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