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 |
Message-ID: | 4111595.1662665620@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
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
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 |