From: Mark Kirkwood <mark.kirkwood@catalyst.net.nz>
To: Shaun Thomas <sthomas@optionshouse.com>
To: Kai Sellgren <kaisellgren@gmail.com>
To: pgsql-performance@postgresql.org
Subject: Re: Optimizing a query
Date: Tue, 24 Dec 2013 13:32:19 +1300
Message-ID: <52B8D613.9030309@catalyst.net.nz> (raw)
In-Reply-To: <52B332B3.9050402@optionshouse.com>
References: <CANyR-3cRezeFXaE6udHh=SEkO0BfAP2Aq5B=eo90n=WrqXnj_A@mail.gmail.com>
<52B332B3.9050402@optionshouse.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-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
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: mark.kirkwood@catalyst.net.nz, sthomas@optionshouse.com, kaisellgren@gmail.com
Subject: Re: Optimizing a query
In-Reply-To: <52B8D613.9030309@catalyst.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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