pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedarray can be slow when joining?
2+ messages / 2 participants
[nested] [flat]
* array can be slow when joining?
@ 2010-08-29 13:18 Volodymyr Kostyrko <c.kworr@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Volodymyr Kostyrko @ 2010-08-29 13:18 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: array can be slow when joining?
@ 2010-08-30 03:53 Pavel Stehule <pavel.stehule@gmail.com>
parent: Volodymyr Kostyrko <c.kworr@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Pavel Stehule @ 2010-08-30 03:53 UTC (permalink / raw)
To: Volodymyr Kostyrko <c.kworr@gmail.com>; +Cc: pgsql-performance
Hello
if you read a some longer field - longer than 2Kb, then PostgreSQL has
to read this value from a table_toast file. see
http://developer.postgresql.org/pgdocs/postgres/storage-toast.html -
and reading have to be slower than you don't read this field.
Regards
Pavel Stehule
2010/8/29 Volodymyr Kostyrko <c.kworr@gmail.com>:
> 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.
>
> --
> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-performance
>
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2010-08-30 03:53 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-08-29 13:18 array can be slow when joining? Volodymyr Kostyrko <c.kworr@gmail.com>
2010-08-30 03:53 ` Pavel Stehule <pavel.stehule@gmail.com>
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