Re: BUG #2658: Query not using index

От: Tom Lane
Тема: Re: BUG #2658: Query not using index
Дата: ,
Msg-id: 25227.1159915172@sss.pgh.pa.us
(см: обсуждение, исходный текст)
Ответ на: Re: BUG #2658: Query not using index  (Mark Lewis)
Список: pgsql-performance

Скрыть дерево обсуждения

Re: BUG #2658: Query not using index  (Graham Davis, )
 Re: BUG #2658: Query not using index  (Chris Browne, )
  Re: BUG #2658: Query not using index  (Graham Davis, )
  Re: BUG #2658: Query not using index  (Graham Davis, )
   Re: BUG #2658: Query not using index  (Bruno Wolff III, )
 Re: BUG #2658: Query not using index  (Chris Browne, )
  Re: BUG #2658: Query not using index  (Graham Davis, )
   Re: BUG #2658: Query not using index  (Tom Lane, )
    Re: BUG #2658: Query not using index  (Graham Davis, )
     Re: BUG #2658: Query not using index  (Mark Lewis, )
      Re: BUG #2658: Query not using index  (Graham Davis, )
       Re: BUG #2658: Query not using index  (Mark Lewis, )
        Re: BUG #2658: Query not using index  (Graham Davis, )
         Re: BUG #2658: Query not using index  (Mark Lewis, )
          Re: BUG #2658: Query not using index  (Tom Lane, )
 PostgreSQL Caching  ("Adnan DURSUN", )
  Re: PostgreSQL Caching  ("Tomeh, Husam", )
   Re: PostgreSQL Caching  ("Adnan DURSUN", )
    Re: PostgreSQL Caching  ("Tomeh, Husam", )
     Re: PostgreSQL Caching  ("Adnan DURSUN", )
      Re: PostgreSQL Caching  ("Dave Dutcher", )
       Re: PostgreSQL Caching  (Brad Nicholson, )
     Re: PostgreSQL Caching  (Brad Nicholson, )

Mark Lewis <> writes:
> Have you considered creating one partial index per assetid?  Something
> along the lines of "CREATE INDEX asset_index_N ON asset_positions(ts)
> WHERE assetid=N"?  I'd guess that the planner probably wouldn't be smart
> enough to use the partial indexes unless you issued a separate query for
> each assetid, but each one of those queries should be really fast.

Actually, a single index on (assetid, ts) is sufficient to handle

    select max(ts) from asset_positions where assetid = constant

The problem is to know what values of "constant" to issue the query for,
and this idea doesn't seem to help with that.

If Graham is willing to assume that the set of assetids changes slowly,
perhaps he could keep a summary table that contains all the valid
assetids (or maybe there already is such a table?  is assetid a foreign
key?) and do

    select pk.assetid,
               (select max(ts) from asset_positions where assetid = pk.assetid)
    from other_table pk;

I'm pretty sure the subselect would be planned the way he wants.

            regards, tom lane


В списке pgsql-performance по дате сообщения:

От: "Adnan DURSUN"
Дата:
Сообщение: Re: PostgreSQL Caching
От: "simon godden"
Дата:
Сообщение: simple case using index on windows but not on linux