Is the query fast with some bind parameters but slow with others?
If so, it'd be better to show an explain with 'fast' and 'slow' bind
params, rather than the same bind params with enable_*=off.
Or is the change because autoanalyze runs on some table and changes the
statistics enough to change th
sometimes, it suddenly
go to bad plan.
schema:
all three tables are hash partitioned tables(partitionkeyid column is the
top index key too), generally speaking with index scan and partition prunning
with partitionkeyid, it's good plan. but sometimes, optimizer suddenly go to
"
On Sat, 6 Jul 2024 at 02:43, James Pang wrote:
>for nest loop path, since the first one estimated only "8" rows ,
> and they use partitionkeyid as joinkey and all are hash partitions , is it
> better to estimate cost to 8 (loop times) * 1600 = 12800 (each one loop map
> to only 1
David Rowley 於 2024年7月5日週五 上午10:15寫道：
> On Fri, 5 Jul 2024 at 12:50, James Pang wrote:
> >we have a daily vacuumdb and analyze job, generally speaking it's
> done in seconds, sometimes it suddenly running more than tens of minutes
> with same bind variable values and huge temp space got used
On Fri, 5 Jul 2024 at 12:50, James Pang wrote:
>we have a daily vacuumdb and analyze job, generally speaking it's done in
> seconds, sometimes it suddenly running more than tens of minutes with same
> bind variable values and huge temp space got used and at that time, explain
> show "Hash
prunning
with partitionkeyid, it's good plan. but sometimes, optimizer suddenly go to
"Hash Right Join" and seq scan on that large table "testaa.wmpct, that made
very
bad plan. from the cost, looks like optimizer try to calculate cost for all
partitions bitmapindex s
Hi James,
I think it'd be much easier to help you with investigating this issue if
you shared the actual queries, and the full EXPLAIN ANALYZE output both
with and without disabled hashjoin. Or even better, share a script that
reproduces the issue (creates tables, loads data, runs the queries).
B
the join is "left out join"
James Pang 於 2024年7月3日週三 下午2:51寫道：
>
> the query is
>select
>from table b join table aa
>on b.partitionkeyid=aa.partitionkeyid
>where b.id1= $1 and b.id2=$2 and b.rtime between $3 and $4;
>
> looks like optimizer try to "calcul
the query is
select
from table b join table aa
on b.partitionkeyid=aa.partitionkeyid
where b.id1= $1 and b.id2=$2 and b.rtime between $3 and $4;
looks like optimizer try to "calculate cost for nestloop for
scanning all partitions of tablexxx (32 hash partitions) "
Both tables are hash partition tables , and we have a left out join ,
optimizer convert to Hash Right Join, but it always try to seq scan on
tablexxx 32 paritions. there are almost 250k rows per parition for
table , so it's slow. As a workaround, I disable hashjoin the it run
much fast with
10 matches
Mail list logo
