Re: num_sa_scans in genericcostestimate

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: num_sa_scans in genericcostestimate
Date: 2022-09-08 19:33:40
Views: Raw Message | Whole Thread | Download mbox | Resend email
Lists: pgsql-hackers

Jeff Janes <jeff(dot)janes(at)gmail(dot)com> writes:
> Why does having the =ANY in the "Index Cond:" rather than the "Filter:"
> inhibit it from understanding that the rows will still be delivered in
> order by "thousand"?

They won't be. The =ANY in index conditions results in multiple
index scans, that is we effectively do a scan with

Index Cond: (thousand < 2) AND (tenthous = 1001)

and then another with

Index Cond: (thousand < 2) AND (tenthous = 3000)

and only by very good luck would the overall result be sorted by
"thousand". On the other hand, if the ScalarArrayOp is a plain
filter condition, then it doesn't affect the number of index
scans --- it's just a (rather expensive) filter condition.

indxpath.c's get_index_paths() is explicitly aware of these
considerations, maybe reading the comments there would help.

I don't say there couldn't be a bug here, but you haven't
demonstrated one. I believe that get_index_paths() will
generate paths both ways, with the ScalarArrayOp as a filter
condition and an indexqual, and it's evidently deciding the
first way is cheaper.

regards, tom lane

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Tom Lane 2022-09-08 19:54:33 Re: [BUG] wrong FK constraint name when colliding name on ATTACH
Previous Message Tom Lane 2022-09-08 19:17:18 Re: num_sa_scans in genericcostestimate