agora inbox for pgsql-performance@postgresql.org
help / color / mirror / Atom feedFrom: Tomas Vondra <tomas.vondra@2ndquadrant.com>
To: pgsql-performance@postgresql.org
Subject: Re: Query performance
Date: Sun, 25 Jan 2015 17:57:36 +0100
Message-ID: <54C52080.90708@2ndquadrant.com> (raw)
In-Reply-To: <CACfv+pJEdGm9_VfQRDAKyLM=RQT95X_0QLx0XQi2T8HqMxXoZQ@mail.gmail.com>
References: <CACfv+pKyJWGcU9DjmL3QAkUcb2cUHb-0sqODmjFt-hC9=k2+Kg@mail.gmail.com>
<CACfv+pJ3GGqm+uwqDWMuZeEJbTp1cNZ5o+KLtPk8w_KNqCf5wg@mail.gmail.com>
<CACfv+p+-7d015+uNJewUttb68nmOioKD72xxr2f7dc5gBcOdKg@mail.gmail.com>
<CAFj8pRAg_VRwRaOBRwVMNtg8KQNs0W82i3EkCCj8_kNBtWtQ-w@mail.gmail.com>
<CACfv+pJEdGm9_VfQRDAKyLM=RQT95X_0QLx0XQi2T8HqMxXoZQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Hi,
On 25.1.2015 07:38, Joe Van Dyk wrote:
>
> Here's one that's not quite as well: http://explain.depesz.com/s/SgT
As Pavel already pointed out, the first problem is this part of the plan:
Seq Scan on events e2 (cost=0.00..120,179.60 rows=4,450,241 width=51)
(actual time=0.014..33,773.370 rows=4,450,865 loops=1)
Filter: (product_id <> '81716'::citext)
Consuming ~33 seconds of the runtime. If you can make this faster
somehow (e.g. by getting rid of the citext cast), that'd be nice.
Another issue is that the hashjoin is batched:
Buckets: 65536 Batches: 8 Memory Usage: 46085kB
The hash preparation takes ~40 seconds, so maybe try to give it a bit
more memory - I assume you have work_mem=64MB, so try doubling that
(ISTM 512MB should work with a single batch). Maybe this won't really
improve the performance, though. It still has to process ~4.5M rows.
Increasing the work mem could also result in switching to hash
aggregate, making the sort (~30 seconds) unnecessary.
Anyway, ISTM this works as expected, i.e.
(a) with rare product_id values the queries are fast
(b) with common product_id values the queries are slow
That's expected, because (b) needs to process much more data. I don't
think you can magically make it run as fast as (a). The best solution
might be to keep a pre-aggregated results - I don't think you really
need exact answers when recommending "similar" products.
I also wonder if you really need to join the tables? I mean, what if you
do something like this:
CREATE TABLE events_aggregated AS SELECT
site_id,
array_agg(product_id) AS product_ids,
count(nullif(e2.type='viewed', false)) view_count,
count(nullif(e2.type='purchased', false)) purchase_count
FROM events
GROUP BY 1;
and then using intarray with GIN indexes to query this table?
Something like this:
CREATE products_agg_idx ON aggregated
USING GIN (product_ids gin__int_ops);
SELECT * FROM events_aggregated WHERE product_ids @> ARRAY['82503'];
regards
--
Tomas Vondra http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
view thread (48+ messages) latest in thread
Message-ID: <54C52080.90708@2ndquadrant.com>
Permalink: ../54C52080.90708@2ndquadrant.com/
Also on: postgresql.org/message-id/54C52080.90708@2ndquadrant.com
reply
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: tomas.vondra@2ndquadrant.com
Subject: Re: Query performance
In-Reply-To: <54C52080.90708@2ndquadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox