Re: Optimizing a query

Поиск
Список
Период
Сортировка
От Mark Kirkwood
Тема Re: Optimizing a query
Дата
Msg-id 52B8D613.9030309@catalyst.net.nz
обсуждение исходный текст
Ответ на Re: Optimizing a query  (Shaun Thomas <sthomas@optionshouse.com>)
Список pgsql-performance
On 20/12/13 06:53, Shaun Thomas wrote:
> On 12/17/2013 08:48 PM, Kai Sellgren wrote:
>
> This is your select:
>
>> SELECT *
>> FROM "Log"
>> LEFT JOIN "NewsArticle" ON "NewsArticle".id = "Log"."targetId" AND
>> "Log"."targetType" = 'NewsArticle'
>> ORDER BY "Log"."createdAt" DESC
>> LIMIT 10
>
> This is your index:
>
>> CREATE INDEX "Log_targetId_targetType_idx"
>>    ON "Log"
>>    USING btree
>>    ("targetId", "targetType" COLLATE pg_catalog."default");
>
> Unfortunately, this won't help you. You are not matching on any IDs you
> indexed, aside from joining against the article table. You have no WHERE
> clause to restrict the data set, so it absolutely must read the entire
> table to find the most recent records. Without an index on "createdAt",
> how is it supposed to know what the ten most recent records are?
>
> Add an index to the createdAt column:
>
> CREATE INDEX idx_log_createdat ON "Log" (createdAt DESC);
>
> Using that, it should get the ten most recent Log records almost
> immediately, including associated article content.
>

Also, might be worth creating an index on NewsArticle(id) so that the
join to this table does not require a full table scan:

CREATE INDEX newsarticle_id_idx ON "NewsArticle" (id);

(probably not a problem when you only have a few articles - but will be
as the volume increases over time).

Regards

Mark



В списке pgsql-performance по дате отправления:

Предыдущее
От: kosalram Babu Chellappa
Дата:
Сообщение: Bytea(TOAST) vs large object facility(OID)
Следующее
От: Albe Laurenz
Дата:
Сообщение: Re: Bytea(TOAST) vs large object facility(OID)