pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedForcing use of specific index
4+ messages / 4 participants
[nested] [flat]
* Forcing use of specific index
@ 2005-06-02 02:05 Tobias Brox <tobias@nordicbet.com>
2005-06-03 23:22 ` Re: Forcing use of specific index William Yu <wyu@talisys.com>
2005-06-04 01:52 ` Re: Forcing use of specific index Junaili Lie <junaili@gmail.com>
2005-06-04 14:14 ` Re: Forcing use of specific index Qingqing Zhou <zhouqq@cs.toronto.edu>
0 siblings, 3 replies; 4+ messages in thread
From: Tobias Brox @ 2005-06-02 02:05 UTC (permalink / raw)
To: pgsql-performance
Is it any way to attempt to force the planner to use some specific index
while creating the plan? Other than eventually dropping all the other
indices (which is obiously not a solution in production setting anyway)?
I have one case where I have added 16 indices to a table, many of them
beeing partial indices. The table itself has only 50k of rows, but are
frequently used in heavy joins. I imagine there can be exponential order on
the number of alternative paths the planner must examinate as function of
the number of indices?
It seems to me that the planner is quite often not choosing the "best"
index, so I wonder if there is any easy way for me to check out what the
planner think about a specific index :-)
--
Tobias Brox, Beijing
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Forcing use of specific index
2005-06-02 02:05 Forcing use of specific index Tobias Brox <tobias@nordicbet.com>
@ 2005-06-03 23:22 ` William Yu <wyu@talisys.com>
2 siblings, 0 replies; 4+ messages in thread
From: William Yu @ 2005-06-03 23:22 UTC (permalink / raw)
To: pgsql-performance
A pretty awful way is to mangle the sql statement so the other field
logical statements are like so:
select * from mytable where 0+field = 100
Tobias Brox wrote:
> Is it any way to attempt to force the planner to use some specific index
> while creating the plan? Other than eventually dropping all the other
> indices (which is obiously not a solution in production setting anyway)?
>
> I have one case where I have added 16 indices to a table, many of them
> beeing partial indices. The table itself has only 50k of rows, but are
> frequently used in heavy joins. I imagine there can be exponential order on
> the number of alternative paths the planner must examinate as function of
> the number of indices?
>
> It seems to me that the planner is quite often not choosing the "best"
> index, so I wonder if there is any easy way for me to check out what the
> planner think about a specific index :-)
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Forcing use of specific index
2005-06-02 02:05 Forcing use of specific index Tobias Brox <tobias@nordicbet.com>
@ 2005-06-04 01:52 ` Junaili Lie <junaili@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Junaili Lie @ 2005-06-04 01:52 UTC (permalink / raw)
To: pgsql-performance
HI all,
I also would like to know if there is a way to force a use of a
specific index for a specific query. I am currently using Postgresql
7.4.6
In my case I have a relatively big table (several millions of records)
that are frequently used to join with other tables (explicit join or
through view).
The table has several indices, some are single column and some are multi column.
Some queries are faster if using single colum index while other are
faster using multi column indexes.
I have play around with SET STATISTICS, but it doesn't seem to make
any differences (I tried to set it to 1000 one time, but still the
same). I did analyze and vacuum after SET STATISTICS.
Any pointer on how to do this is greatly appreciated.
Thank you in advance,
J
On 6/1/05, Tobias Brox <tobias@nordicbet.com> wrote:
> Is it any way to attempt to force the planner to use some specific index
> while creating the plan? Other than eventually dropping all the other
> indices (which is obiously not a solution in production setting anyway)?
>
> I have one case where I have added 16 indices to a table, many of them
> beeing partial indices. The table itself has only 50k of rows, but are
> frequently used in heavy joins. I imagine there can be exponential order on
> the number of alternative paths the planner must examinate as function of
> the number of indices?
>
> It seems to me that the planner is quite often not choosing the "best"
> index, so I wonder if there is any easy way for me to check out what the
> planner think about a specific index :-)
>
> --
> Tobias Brox, Beijing
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Forcing use of specific index
2005-06-02 02:05 Forcing use of specific index Tobias Brox <tobias@nordicbet.com>
@ 2005-06-04 14:14 ` Qingqing Zhou <zhouqq@cs.toronto.edu>
2 siblings, 0 replies; 4+ messages in thread
From: Qingqing Zhou @ 2005-06-04 14:14 UTC (permalink / raw)
To: pgsql-performance
"Tobias Brox" <tobias@nordicbet.com> writes
> Is it any way to attempt to force the planner to use some specific index
> while creating the plan? Other than eventually dropping all the other
> indices (which is obiously not a solution in production setting anyway)?
>
I don't think currently PG supports this but "SQL hints" is planned ...
Regards,
Qingqing
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-06-04 14:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-06-02 02:05 Forcing use of specific index Tobias Brox <tobias@nordicbet.com>
2005-06-03 23:22 ` William Yu <wyu@talisys.com>
2005-06-04 01:52 ` Junaili Lie <junaili@gmail.com>
2005-06-04 14:14 ` Qingqing Zhou <zhouqq@cs.toronto.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox