Re: Optimizing a query

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