Обсуждение: Nested loops are killing throughput
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
criteriais 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.21rows=1 width=30) (actual time=10767.525..10767.989 rows=360 loops=1) Sort Key:
(dpdl.field_value)::timestampwith time zone -> Nested Loop (cost=2.01..762.20 rows=1 width=30) (actual
time=283.824..10749.007rows=360 loops=1) -> Nested Loop (cost=0.00..744.28 rows=1 width=16) (actual
time=31.227..6980.765rows=5436 loops=1) -> Nested Loop (cost=0.00..135.29 rows=101 width=16)
(actualtime=25.514..273.660 rows=5436 loops=1) -> Index Scan using ut_company_name_idx
onuser_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_idxon 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)
(actualtime=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_datalinkdpdl (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.
...
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=5436loops=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
----- 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=5436loops=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=2225loops=1) Index Cond: ((username)::text = 'joeuser'::text) -> Index Scan using packet_status_puuid_pkey
onpacket_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
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.519rows=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.117rows=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
>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 seemslike 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 comeacross 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.
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
>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() shouldbe VOLATILE and that's about it) and set them to STRICT as well. I haven't had a problem with it crashing my backendin 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 youend up with a standard UUID type, a fixed field type with multiple generators for differing levels of paranoia, or thestatus quo. CG
>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!