Message-ID: <52B8D613.9030309@catalyst.net.nz>
Date: Tue, 24 Dec 2013 13:32:19 +1300
From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
User-Agent: Mozilla/5.0 (X11; Linux x86_64;
 rv:24.0) Gecko/20100101 Thunderbird/24.2.0
MIME-Version: 1.0
To: Shaun Thomas <sthomas@optionshouse.com>,
 Kai Sellgren <kaisellgren@gmail.com>, pgsql-performance@postgresql.org
Subject: Re: Optimizing a query
References: 
 <CANyR-3cRezeFXaE6udHh=SEkO0BfAP2Aq5B=eo90n=WrqXnj_A@mail.gmail.com>
 <52B332B3.9050402@optionshouse.com>
In-Reply-To: <52B332B3.9050402@optionshouse.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

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


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance