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
Sorry for confusion, it's from attached explain output of the SQL.
please check attached. my questions is : for nestloop of two partition
tables , they use same partition key and equal join on partition key, the
cost could be "outer tables estimated rows" * (average index scan of only
one
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
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 Anti join, Hash Right join" with seq scan two tables.
Tom
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) "
