Optimizing a query

From: Kai Sellgren <kaisellgren(at)gmail(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Subject: Optimizing a query
Date: 2013-12-18 02:48:10
Message-ID: CANyR-3cRezeFXaE6udHh=SEkO0BfAP2Aq5B=eo90n=WrqXnj_A@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hi,

I'm new to PostgreSQL and trying to run this query:

SELECT *
FROM "Log"
LEFT JOIN "NewsArticle" ON "NewsArticle".id = "Log"."targetId" AND
"Log"."targetType" = 'NewsArticle'
ORDER BY "Log"."createdAt" DESC
LIMIT 10

Basically I'm finding the last 10 log entries, which point (targetType) to
news articles.

The explain analyze is this:

http://d.pr/i/mZhl (I didn't know how to copy from the pgAdmin, without
having a huge mess)

I have this index on Log:

CREATE INDEX "Log_targetId_targetType_idx"
ON "Log"
USING btree
("targetId", "targetType" COLLATE pg_catalog."default");

I have ran Vacuum and Analyze on both tables.

What am I missing here?

--
Yours sincerely,
Kai Sellgren

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Guillaume Lelarge 2013-12-18 08:30:27 Re: Current query of the PL/pgsql procedure.
Previous Message Stephen Frost 2013-12-17 14:01:28 Re: Adding an additional join causes very different/slow query plan