From: Volodymyr Kostyrko <c.kworr@gmail.com>
To: pgsql-performance@postgresql.org
Subject: array can be slow when joining?
Date: Sun, 29 Aug 2010 16:18:22 +0300
Message-ID: <4C7A5E1E.6050205@gmail.com> (raw)
I don't clearly understand why this happens, but when I try join some
tables using arrays I end up with:
=# explain select count(*) from urls JOIN rules ON urls.tag && rules.tag;
QUERY PLAN
---------------------------------------------------------------------------------------
Aggregate (cost=1356.27..1356.28 rows=1 width=0)
-> Nested Loop (cost=20.33..1354.96 rows=523 width=0)
-> Seq Scan on rules (cost=0.00..1.01 rows=1 width=37)
-> Bitmap Heap Scan on urls (cost=20.33..1347.42 rows=523
width=29)
Recheck Cond: (urls.tag && rules.tag)
-> Bitmap Index Scan on url_tag_g (cost=0.00..20.20
rows=523 width=0)
Index Cond: (urls.tag && rules.tag)
Here tag is text[] with list of tags. Whole select takes 142 ms. It
drops down to 42 ms when I add some conditions that strip result table
to zero length.
What am I missing? Is there any other ways to overlap those ones? Or
should I find "any other way"?
--
Sphinx of black quartz judge my vow.
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: c.kworr@gmail.com
Subject: Re: array can be slow when joining?
In-Reply-To: <4C7A5E1E.6050205@gmail.com>
* 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