search

ossc-db / pg_hint_plan

Extension adding support for optimizer hints in PostgreSQL
Other
696 stars 103 forks source link

Is there a way to hint the exact list (and order) of columns used by a specific composite index? #120

Closed vladich closed 1 year ago

vladich commented 1 year ago

Some index types (GiST as an example) allow scanning by any combination of columns in a composite index. This can result in completely different execution plans / timing (if we specify just index name). Is there a way to fine-tune it to specify the list of columns as well?

vladich commented 1 year ago

If there is no such a way currently, is there any fundamental reason why it can't be implemented? Does planner support that kind of stuff?

vladich commented 1 year ago

anybody?

michaelpq commented 1 year ago

If you can show up a simple SQL example of what you are trying to achieve, somebody may be able to help more easily, I guess. For now I am just closing this issue as this is no bug.

vladich commented 12 months ago

If you can show up a simple SQL example of what you are trying to achieve, somebody may be able to help more easily, I guess. For now I am just closing this issue as this is no bug.

A very simple thing really. It's actually possible even with btree, not necessarily exotic index types. Something like:

SELECT * FROM table1 WHERE column1 = 'value1' AND column2 = 'value2' AND column3 = 'value3'

We have a primary key by all 3 columns on the table. What I've seen recently is this:

Index Scan using table1_pkey on schema1.table1 (cost=0.70..3.73 rows=1 width=8) (actual time=11783.525..11783.525 rows=0 loops=1) Index Cond: (((table1.column1)::text = ($1)::text) AND ((table1.column3)::text = ($3)::text)) Filter: ((table1.column2)::text = ($2)::text) Rows Removed by Filter: 8804043 Buffers: shared hit=9256173 Query Identifier: 5413874536952925371 Planning Time: 0.006 ms Execution Time: 11783.605 ms

It looks completely crazy, because table1_pkey looks like this: create unique index table1_pkey on schema1.table1 (column1, column2, column3); all 3 columns are varchars.

the table has ~40M rows. Enforcing the primary key doesn't help obviously, because it's used already. What I was asking about is if it's possible to provide a way to specify not just index name, but optionally a list of columns that should be used for index scan.