[postgis-users] ERROR: array size exceeds themaximumallowed(134217727)
Mike Leahy
mgleahy at alumni.uwaterloo.ca
Sat Mar 20 21:29:40 PDT 2010
Leo/Regina,
In response to your earlier message, I did find that report of the identical
error message. However, as I'm sure you noticed, there is no apparent
investigation that followed after it. That was why I went to the PostgreSQL
IRC channel first...they walked me through generating a core dump, which is
what then pointed to it being a problem related to PostGIS. So while it's
certainly the same error being reported, the source of the problem in this
case appears PostGIS-specific - likely due to the logic that you note was
borrowed from the array_agg that caused the problem in the previous report.
As for looking at the different query plans for working/non-working versions of
the statement with different where conditions, I've attached several results in
text files. Two examples that worked, one that segfaults, and one that
produces the 'array size exceeds...' error message. All of these are the same
query, just with variations in the where condition. The query plan is a but
much for me to interpret though - what does this look like to the experts?
Mike
On Saturday 20 March 2010 23:35:32 Paragon Corporation wrote:
> Mike,
> Another thought. I suppose it could be the more or less two identical
> subselects both with array_aggs
> Just to give Paul something hopefully shorter to work with. Does this fail
> too?
>
> SELECT csls.chat_id, csls.ugeom
> FROM (
>
> SELECT '[stuff]' AS selection, st_union(geom) AS ugeom, csl1.chat_id
> FROM testdb.user_selections AS us1
> INNER JOIN testdb.chat_selection_links AS csl1 ON us1.id =
> csl1.selection_id
> WHERE (not us1.user_drawing and not csl1.deleted)
> GROUP BY chat_id
>
> ) AS csls
>
> LEFT JOIN (
>
> SELECT '[stuff]' AS drawing, st_union(geom) AS ugeom, csl2.chat_id
> FROM testdb.user_selections AS us2
> INNER JOIN testdb.chat_selection_links AS csl2 ON us2.id =
> csl2.selection_id
> WHERE (us2.user_drawing and us2.deleted and not csl2.deleted)
> GROUP BY chat_id
>
> ) AS csld ON csls.chat_id = csld.chat_id
>
> Leo and Regina,
> http://www.postgis.us
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paragon
> Corporation
> Sent: Saturday, March 20, 2010 11:29 PM
> To: 'PostGIS Users Discussion'; mgleahy at alumni.uwaterloo.ca
> Subject: Re: [postgis-users] ERROR: array size exceeds
> themaximumallowed(134217727)
>
> Mike,
> Actually scanning thru the archives that 13... is a common number so
> suspect that is an OS limit.
>
> This could be very well a bug in PostgreSQL 8.4.
>
> Have you tried this on other PostgreSQL 8.4 installs on other your Fedoras
> - you mentioned trying on older installs?
>
> Your problem sounds exactly like this guy's and he wasn't using PostGIS at
> all but array_agg (which is logic that PostGIS borrowed from to implement
> the faster collecting ST_Union behavior)i
> http://archives.postgresql.org/pgsql-hackers/2009-06/msg01171.php
>
> If you haven't already probably good to check the plan difference between
> the two queries (one with addtional WHERE and one without). It could be
> doing something nuts like running the sub select for each record you have
> in the outer only when your extra WHERE condition is added and doing more
> or less the sane thing when you take it out.
>
> We've had some odd behavior in 8.4 with the planner doing strange things
> that we haven't been able to pin down and had to implement workaround for.
> As we described in this article.. Its probably unrelated though but just a
> thought to throw out at the wind.
>
> http://www.postgresonline.com/journal/index.php?/archives/149-Forcing-the-p
> l anners-hand-with-set-enable_seqscan-off-WTF.html
>
> Leo and Regina,
> http://www.postgis.us
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Paul
> Ramsey
> Sent: Saturday, March 20, 2010 8:23 PM
> To: mgleahy at alumni.uwaterloo.ca; PostGIS Users Discussion
> Subject: Re: [postgis-users] ERROR: array size exceeds the
> maximumallowed(134217727)
>
> Mike
>
> Well, if you can reduce it to a small db dump and query that exercises it,
> I can install a 64bit ubuntu VM and see if it happens here too.
>
> P
>
> On Sat, Mar 20, 2010 at 3:30 PM, Mike Leahy <mgleahy at alumni.uwaterloo.ca>
>
> wrote:
> > FWIW, if I remove the entire where clause at the end of the
> > statement, it works again (with st_union()) and is virtually
>
> instantaneous.
>
> > On Saturday 20 March 2010 18:10:38 Mike Leahy wrote:
> >> Hey,
> >>
> >> The query (when it works) is virtually instantaneous when I use
> >> st_memunion() (or when I took out part of the where clause -
> >> although now
> >> I can't even get that to work, so maybe it was just lucky). Each of
> >> the
> >> subqueries also work fine without any obvious problem.
> >>
> >> To me, it seems to be a combination of things that somehow add up to
> >> this limitation...I don't see what settings could affect the array
> >> size limit
> >> (in postgresql.conf). Can anyone point to a config option that
> >> might make
> >> a difference, or maybe point to another place with settings I can
> >> tinker
> >> with?
> >>
> >> Mike
> >>
> >> On Saturday 20 March 2010 17:53:24 Paragon Corporation wrote:
> >> > Mike,
> >> >
> >> > We apologize, didn't notice this was in a subquery and that you
> >> > have a
> >> > limit statement in your query. So we presume regardless of your
> >> > WHERE
> >> > only 26 records are being selected.
> >> >
> >> > So Paul could be right that you do have data that is hitting some
> >> > compiled or variable limit.
> >> >
> >> > Does running the subquery alone work or you didn't try because it
> >> > takes a long time?
> >> >
> >> > Thanks,
> >> > Leo and Regina
> >> > http://www.postgis.us
> >> >
> >> > -----Original Message-----
> >> > From: Mike Leahy [mailto:mgl.gis at gmail.com] On Behalf Of Mike Leahy
> >> > Sent: Saturday, March 20, 2010 2:01 PM
> >> > To: Paragon Corporation
> >> > Cc: 'PostGIS Users Discussion'
> >> > Subject: Re: [postgis-users] ERROR: array size exceeds the maximum
> >> > allowed(134217727)
> >> >
> >> > Hi Leo/Regina,
> >> >
> >> > Thanks for the suggestion. I tried adding the clause 'not geom is
>
> null'
>
> >> > to the where statement in each of the two sub-queries that have the
> >> > st_union(geom) functions are used, but it still segfaults.
> >> >
> >> > I also tried this on a fresh database with very little data, and it
> >> > doesn't seem to cause problems. But I have two databases with live
> >> > data where I can cause this. I have been able to pare one of these
> >> > down to remove personal information and reduce unnecessary data,
> >> > while still generating the crash with that query. Would someone
> >> > be interested in a dump of this db? Of course, that someone would
> >> > ideally be able to test this on a 64-bit (K)ubutnu system, in the
> >> > hopes that the problem can be replicated.
> >> >
> >> > Mike
> >> >
> >> > On Saturday 20 March 2010 11:32:35 Paragon Corporation wrote:
> >> > > Paul,
> >> > > I doubt array size limit is the issue. He said when he left the
> >> > > where condition out it worked. I would think it would definitely
> >> > > blow up in that case.
> >> > >
> >> > > Mike,
> >> > > The issue from before was that array aggregate functions did not
> >> > > handle NULLs correctly. 64-bit systems were more likely to
> >> > > segfault or give strange Errros in this case.
> >> > >
> >> > > To rule out that we still have some of these issues in the code
> >> > > base, can you add a
> >> > >
> >> > > geom IS NOT NULL
> >> > >
> >> > > Condition to your WHERE filter. If that works, then the NULL
> >> > > issue is probably still lurking somewhere.
> >> > >
> >> > > Leo and Regina
> >> > > http://www.postgis.us
> >> > >
> >> > >
> >> > > -----Original Message-----
> >> > > From: postgis-users-bounces at postgis.refractions.net
> >> > > [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf
> >> > > Of Paul Ramsey
> >> > > Sent: Saturday, March 20, 2010 10:56 AM
> >> > > To: PostGIS Users Discussion; mgleahy at alumni.uwaterloo.ca
> >> > > Subject: Re: [postgis-users] ERROR: array size exceeds the
> >> > > maximum
> >> > > allowed(134217727)
> >> > >
> >> > > Actually, memunion does the opposite, it passes the resultant and
> >> > > preserves mem. The default behavior is fast-but-memory-hungry.
> >> > > And has been for some time, though in different forms. There were
> >> > > some bugs in the array handling code, but Mark CA killed most of
> >> > > them, so the latest 1.5 and 1.4 streams should be good. If it's
> >> > > possible that the issue is one of array size, maybe Mike could
> >> > > find the dial that controls that maximum, and turn it up and down
> >> > > and see if it makes his
> >> >
> >> > problem go away/happen sooner.
> >> >
> >> > > P.
> >> > >
> >> > > On Sat, Mar 20, 2010 at 7:41 AM, strk <strk at keybit.net> wrote:
> >> > > > On Sat, Mar 20, 2010 at 05:49:42AM -0400, Mike Leahy wrote:
> >> > > >> Hello again,
> >> > > >>
> >> > > >> It might be of interest to point out that substituting
> >> > > >> st_union() with
> >> > > >> st_memunion() seems to have worked around this. I'm curious
> >> > > >> though, because there is not a great deal of data being
> >> > > >> processed, and I am running this on a fairly sturdy system
> >> > > >> that that has more capacity than some of the Fedora systems
> >> > > >> I'm
>
> running.
>
> >> > > > st_memunion builds a big array with all geometries in it..
> >> > > > you were hitting a limit of the array type.
> >> > > > st_union should behave better.
> >> > > >
> >> > > > --strk;
> >> > > >
> >> > > > () Free GIS & Flash consultant/developer
> >> > > > /\ http://strk.keybit.net/services.html
> >> > > > _______________________________________________
> >> > > > postgis-users mailing list
> >> > > > postgis-users at postgis.refractions.net
> >> > > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >> > >
> >> > > _______________________________________________
> >> > > postgis-users mailing list
> >> > > postgis-users at postgis.refractions.net
> >> > > http://postgis.refractions.net/mailman/listinfo/postgis-users
> >
> > _______________________________________________
> > postgis-users mailing list
> > postgis-users at postgis.refractions.net
> > http://postgis.refractions.net/mailman/listinfo/postgis-users
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at postgis.refractions.net
> http://postgis.refractions.net/mailman/listinfo/postgis-users
>
-------------- next part --------------
explain SELECT tdesc.* FROM (
SELECT cl.id, cl.parent_id, cl.username, cl.type, cl.text, cl.time, cl.flags, cl.depth, '[stuff]' as extent, csls.selection, csld.drawing, round(date_part('epoch',time)*1000.0) AS epoch_ms, csls.selection, csld.drawing, ctu.target_users, ctg.target_groups, clr.replies, case when (num_target_users>0 or num_target_groups>0) then true else false end AS private FROM bv_mc2.chat_log AS cl
LEFT JOIN (
SELECT '[stuff]' AS selection, st_union(geom) AS ugeom, csl1.chat_id FROM bv_mc2.user_selections AS us1
INNER JOIN bv_mc2.chat_selection_links AS csl1 ON us1.id = csl1.selection_id WHERE (not us1.user_drawing and not csl1.deleted) GROUP BY chat_id
) AS csls ON csls.chat_id = cl.id
LEFT JOIN (
SELECT '[stuff]' AS drawing, st_union(geom) AS ugeom, csl2.chat_id FROM bv_mc2.user_selections AS us2
INNER JOIN bv_mc2.chat_selection_links AS csl2 ON us2.id = csl2.selection_id WHERE (us2.user_drawing and us2.deleted and not csl2.deleted) GROUP BY chat_id
) AS csld ON csld.chat_id = cl.id
LEFT JOIN (
SELECT chat_target_users.id, count(*) AS num_target_users, '[stuff]'::varchar AS target_users, ARRAY['stuff']::text[] AS target_users_array FROM bv_mc2.chat_target_users GROUP BY id
) AS ctu ON cl.id = ctu.id
LEFT JOIN (
SELECT chat_target_groups.id, count(*) AS num_target_groups, '[stuff]'::varchar AS target_groups, ARRAY['stuff']::text[] AS target_groups_array FROM bv_mc2.chat_target_groups GROUP BY id
) AS ctg ON cl.id = ctg.id
LEFT JOIN (
SELECT chat_log.parent_id AS id, '[stuff]' AS replies FROM bv_mc2.chat_log GROUP BY parent_id
) AS clr ON cl.id = clr.id WHERE ((num_target_users is null and num_target_groups is null) or (num_target_users+num_target_groups)=0 or ((cl.username = 'admin' or 'admin' = any(target_users_array) or ARRAY['admin','everyone'] && target_groups_array))) AND (not deleted) ORDER BY cl.id desc LIMIT 25
) AS tdesc ORDER BY tdesc.id asc;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=89.44..89.46 rows=7 width=385)
Sort Key: cl.id
-> Limit (cost=89.25..89.27 rows=7 width=229)
-> Sort (cost=89.25..89.27 rows=7 width=229)
Sort Key: cl.id
-> Hash Left Join (cost=80.71..89.16 rows=7 width=229)
Hash Cond: (cl.id = csld.chat_id)
-> Hash Left Join (cost=62.45..70.77 rows=7 width=197)
Hash Cond: (cl.id = clr.id)
-> Hash Left Join (cost=57.87..66.13 rows=7 width=165)
Hash Cond: (cl.id = csls.chat_id)
-> Hash Left Join (cost=38.15..46.38 rows=7 width=133)
Hash Cond: (cl.id = ctu.id)
Filter: (((ctu.num_target_users IS NULL) AND (ctg.num_target_groups IS NULL)) OR ((ctu.num_target_users + ctg.num_target_groups) = 0) OR ((cl.username)::text = 'admin'::text) OR ('admin'::text = ANY (ctu.target_users_array)) OR ('{admin,everyone}'::text[] && ctg.target_groups_array))
-> Hash Left Join (cost=35.45..42.20 rows=100 width=125)
Hash Cond: (cl.id = ctg.id)
-> Seq Scan on chat_log cl (cost=0.00..3.00 rows=100 width=53)
Filter: (NOT deleted)
-> Hash (cost=32.95..32.95 rows=200 width=76)
-> Subquery Scan ctg (cost=28.45..32.95 rows=200 width=76)
-> HashAggregate (cost=28.45..30.95 rows=200 width=4)
-> Seq Scan on chat_target_groups (cost=0.00..22.30 rows=1230 width=4)
-> Hash (cost=2.27..2.27 rows=34 width=76)
-> Subquery Scan ctu (cost=1.51..2.27 rows=34 width=76)
-> HashAggregate (cost=1.51..1.94 rows=34 width=4)
-> Seq Scan on chat_target_users (cost=0.00..1.34 rows=34 width=4)
-> Hash (cost=19.47..19.47 rows=20 width=36)
-> Subquery Scan csls (cost=19.02..19.47 rows=20 width=36)
-> HashAggregate (cost=19.02..19.27 rows=20 width=22665)
-> Hash Join (cost=2.11..18.80 rows=44 width=22665)
Hash Cond: (us1.id = csl1.selection_id)
-> Seq Scan on user_selections us1 (cost=0.00..15.41 rows=223 width=22665)
Filter: (NOT user_drawing)
-> Hash (cost=1.56..1.56 rows=44 width=8)
-> Seq Scan on chat_selection_links csl1 (cost=0.00..1.56 rows=44 width=8)
Filter: (NOT deleted)
-> Hash (cost=4.07..4.07 rows=41 width=36)
-> Subquery Scan clr (cost=3.25..4.07 rows=41 width=36)
-> HashAggregate (cost=3.25..3.66 rows=41 width=4)
-> Seq Scan on chat_log (cost=0.00..3.00 rows=100 width=4)
-> Hash (cost=18.04..18.04 rows=18 width=36)
-> Subquery Scan csld (cost=17.63..18.04 rows=18 width=36)
-> HashAggregate (cost=17.63..17.86 rows=18 width=22665)
-> Hash Join (cost=15.63..17.54 rows=18 width=22665)
Hash Cond: (csl2.selection_id = us2.id)
-> Seq Scan on chat_selection_links csl2 (cost=0.00..1.56 rows=44 width=8)
Filter: (NOT deleted)
-> Hash (cost=15.41..15.41 rows=18 width=22665)
-> Seq Scan on user_selections us2 (cost=0.00..15.41 rows=18 width=22665)
Filter: (user_drawing AND deleted)
(50 rows)
-------------- next part --------------
explain SELECT tdesc.* FROM (
SELECT cl.id, cl.parent_id, cl.username, cl.type, cl.text, cl.time, cl.flags, cl.depth, '[stuff]' as extent, csls.selection, csld.drawing, round(date_part('epoch',time)*1000.0) AS epoch_ms, csls.selection, csld.drawing, ctu.target_users, ctg.target_groups, clr.replies, case when (num_target_users>0 or num_target_groups>0) then true else false end AS private FROM bv_mc2.chat_log AS cl
LEFT JOIN (
SELECT '[stuff]' AS selection, st_union(geom) AS ugeom, csl1.chat_id FROM bv_mc2.user_selections AS us1
INNER JOIN bv_mc2.chat_selection_links AS csl1 ON us1.id = csl1.selection_id WHERE (not us1.user_drawing and not csl1.deleted) GROUP BY chat_id
) AS csls ON csls.chat_id = cl.id
LEFT JOIN (
SELECT '[stuff]' AS drawing, st_union(geom) AS ugeom, csl2.chat_id FROM bv_mc2.user_selections AS us2
INNER JOIN bv_mc2.chat_selection_links AS csl2 ON us2.id = csl2.selection_id WHERE (us2.user_drawing and us2.deleted and not csl2.deleted) GROUP BY chat_id
) AS csld ON csld.chat_id = cl.id
LEFT JOIN (
SELECT chat_target_users.id, count(*) AS num_target_users, '[stuff]'::varchar AS target_users, ARRAY['stuff']::text[] AS target_users_array FROM bv_mc2.chat_target_users GROUP BY id
) AS ctu ON cl.id = ctu.id
LEFT JOIN (
SELECT chat_target_groups.id, count(*) AS num_target_groups, '[stuff]'::varchar AS target_groups, ARRAY['stuff']::text[] AS target_groups_array FROM bv_mc2.chat_target_groups GROUP BY id
) AS ctg ON cl.id = ctg.id
LEFT JOIN (
SELECT chat_log.parent_id AS id, '[stuff]' AS replies FROM bv_mc2.chat_log GROUP BY parent_id
) AS clr ON cl.id = clr.id WHERE ((num_target_users is null and num_target_groups is null) or (num_target_users+num_target_groups)=0 or ((cl.username = 'admin' or 'admin' = any(target_users_array) or ARRAY['admin','everyone'] && target_groups_array) and (time>'2010-03-20 00:52:57.230968-04'::timestamptz))) AND (not deleted) AND (cl.depth = 0) ORDER BY cl.id desc LIMIT 25
) AS tdesc ORDER BY tdesc.id asc;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=87.05..87.06 rows=1 width=385)
Sort Key: cl.id
-> Limit (cost=87.03..87.03 rows=1 width=229)
-> Sort (cost=87.03..87.03 rows=1 width=229)
Sort Key: cl.id
-> Nested Loop Left Join (cost=78.05..87.02 rows=1 width=229)
Join Filter: (csl2.chat_id = cl.id)
-> Nested Loop Left Join (cost=60.42..68.74 rows=1 width=197)
Join Filter: (cl.id = chat_log.parent_id)
-> Nested Loop Left Join (cost=57.17..64.16 rows=1 width=165)
Join Filter: (csl1.chat_id = cl.id)
-> Hash Left Join (cost=38.15..44.45 rows=1 width=133)
Hash Cond: (cl.id = ctu.id)
Filter: (((ctu.num_target_users IS NULL) AND (ctg.num_target_groups IS NULL)) OR ((ctu.num_target_users + ctg.num_target_groups) = 0) OR ((((cl.username)::text = 'admin'::text) OR ('admin'::text = ANY (ctu.target_users_array)) OR ('{admin,everyone}'::text[] && ctg.target_groups_array)) AND (cl."time" > '2010-03-20 00:52:57.230968-04'::timestamp with time zone)))
-> Hash Left Join (cost=35.45..40.39 rows=45 width=125)
Hash Cond: (cl.id = ctg.id)
-> Seq Scan on chat_log cl (cost=0.00..3.25 rows=45 width=53)
Filter: ((NOT deleted) AND (depth = 0))
-> Hash (cost=32.95..32.95 rows=200 width=76)
-> Subquery Scan ctg (cost=28.45..32.95 rows=200 width=76)
-> HashAggregate (cost=28.45..30.95 rows=200 width=4)
-> Seq Scan on chat_target_groups (cost=0.00..22.30 rows=1230 width=4)
-> Hash (cost=2.27..2.27 rows=34 width=76)
-> Subquery Scan ctu (cost=1.51..2.27 rows=34 width=76)
-> HashAggregate (cost=1.51..1.94 rows=34 width=4)
-> Seq Scan on chat_target_users (cost=0.00..1.34 rows=34 width=4)
-> HashAggregate (cost=19.02..19.27 rows=20 width=22665)
-> Hash Join (cost=2.11..18.80 rows=44 width=22665)
Hash Cond: (us1.id = csl1.selection_id)
-> Seq Scan on user_selections us1 (cost=0.00..15.41 rows=223 width=22665)
Filter: (NOT user_drawing)
-> Hash (cost=1.56..1.56 rows=44 width=8)
-> Seq Scan on chat_selection_links csl1 (cost=0.00..1.56 rows=44 width=8)
Filter: (NOT deleted)
-> HashAggregate (cost=3.25..3.66 rows=41 width=4)
-> Seq Scan on chat_log (cost=0.00..3.00 rows=100 width=4)
-> HashAggregate (cost=17.63..17.86 rows=18 width=22665)
-> Hash Join (cost=15.63..17.54 rows=18 width=22665)
Hash Cond: (csl2.selection_id = us2.id)
-> Seq Scan on chat_selection_links csl2 (cost=0.00..1.56 rows=44 width=8)
Filter: (NOT deleted)
-> Hash (cost=15.41..15.41 rows=18 width=22665)
-> Seq Scan on user_selections us2 (cost=0.00..15.41 rows=18 width=22665)
Filter: (user_drawing AND deleted)
(44 rows)
-------------- next part --------------
explain SELECT tdesc.* FROM (
SELECT cl.id, cl.parent_id, cl.username, cl.type, cl.text, cl.time, cl.flags, cl.depth, '[stuff]' as extent, csls.selection, csld.drawing, round(date_part('epoch',time)*1000.0) AS epoch_ms, csls.selection, csld.drawing, ctu.target_users, ctg.target_groups, clr.replies, case when (num_target_users>0 or num_target_groups>0) then true else false end AS private FROM bv_mc2.chat_log AS cl
LEFT JOIN (
SELECT '[stuff]' AS selection, st_union(geom) AS ugeom, csl1.chat_id FROM bv_mc2.user_selections AS us1
INNER JOIN bv_mc2.chat_selection_links AS csl1 ON us1.id = csl1.selection_id WHERE (not us1.user_drawing and not csl1.deleted) GROUP BY chat_id
) AS csls ON csls.chat_id = cl.id
LEFT JOIN (
SELECT '[stuff]' AS drawing, st_union(geom) AS ugeom, csl2.chat_id FROM bv_mc2.user_selections AS us2
INNER JOIN bv_mc2.chat_selection_links AS csl2 ON us2.id = csl2.selection_id WHERE (us2.user_drawing and us2.deleted and not csl2.deleted) GROUP BY chat_id
) AS csld ON csld.chat_id = cl.id
LEFT JOIN (
SELECT chat_target_users.id, count(*) AS num_target_users, '[stuff]'::varchar AS target_users, ARRAY['stuff']::text[] AS target_users_array FROM bv_mc2.chat_target_users GROUP BY id
) AS ctu ON cl.id = ctu.id
LEFT JOIN (
SELECT chat_target_groups.id, count(*) AS num_target_groups, '[stuff]'::varchar AS target_groups, ARRAY['stuff']::text[] AS target_groups_array FROM bv_mc2.chat_target_groups GROUP BY id
) AS ctg ON cl.id = ctg.id
LEFT JOIN (
SELECT chat_log.parent_id AS id, '[stuff]' AS replies FROM bv_mc2.chat_log GROUP BY parent_id
) AS clr ON cl.id = clr.id WHERE ((num_target_users is null and num_target_groups is null) or (num_target_users+num_target_groups)=0 or ((cl.username = 'admin' or 'admin' = any(target_users_array) or ARRAY['admin','everyone'] && target_groups_array))) AND (not deleted) AND (cl.depth = 0) ORDER BY cl.id desc LIMIT 25
) AS tdesc ORDER BY tdesc.id asc;
QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=87.13..87.13 rows=3 width=385)
Sort Key: cl.id
-> Limit (cost=87.07..87.07 rows=3 width=229)
-> Sort (cost=87.07..87.07 rows=3 width=229)
Sort Key: cl.id
-> Hash Left Join (cost=80.71..87.04 rows=3 width=229)
Hash Cond: (cl.id = csld.chat_id)
-> Hash Left Join (cost=62.45..68.72 rows=3 width=197)
Hash Cond: (cl.id = clr.id)
-> Hash Left Join (cost=57.87..64.10 rows=3 width=165)
Hash Cond: (cl.id = csls.chat_id)
-> Hash Left Join (cost=38.15..44.36 rows=3 width=133)
Hash Cond: (cl.id = ctu.id)
Filter: (((ctu.num_target_users IS NULL) AND (ctg.num_target_groups IS NULL)) OR ((ctu.num_target_users + ctg.num_target_groups) = 0) OR ((cl.username)::text = 'admin'::text) OR ('admin'::text = ANY (ctu.target_users_array)) OR ('{admin,everyone}'::text[] && ctg.target_groups_array))
-> Hash Left Join (cost=35.45..40.39 rows=45 width=125)
Hash Cond: (cl.id = ctg.id)
-> Seq Scan on chat_log cl (cost=0.00..3.25 rows=45 width=53)
Filter: ((NOT deleted) AND (depth = 0))
-> Hash (cost=32.95..32.95 rows=200 width=76)
-> Subquery Scan ctg (cost=28.45..32.95 rows=200 width=76)
-> HashAggregate (cost=28.45..30.95 rows=200 width=4)
-> Seq Scan on chat_target_groups (cost=0.00..22.30 rows=1230 width=4)
-> Hash (cost=2.27..2.27 rows=34 width=76)
-> Subquery Scan ctu (cost=1.51..2.27 rows=34 width=76)
-> HashAggregate (cost=1.51..1.94 rows=34 width=4)
-> Seq Scan on chat_target_users (cost=0.00..1.34 rows=34 width=4)
-> Hash (cost=19.47..19.47 rows=20 width=36)
-> Subquery Scan csls (cost=19.02..19.47 rows=20 width=36)
-> HashAggregate (cost=19.02..19.27 rows=20 width=22665)
-> Hash Join (cost=2.11..18.80 rows=44 width=22665)
Hash Cond: (us1.id = csl1.selection_id)
-> Seq Scan on user_selections us1 (cost=0.00..15.41 rows=223 width=22665)
Filter: (NOT user_drawing)
-> Hash (cost=1.56..1.56 rows=44 width=8)
-> Seq Scan on chat_selection_links csl1 (cost=0.00..1.56 rows=44 width=8)
Filter: (NOT deleted)
-> Hash (cost=4.07..4.07 rows=41 width=36)
-> Subquery Scan clr (cost=3.25..4.07 rows=41 width=36)
-> HashAggregate (cost=3.25..3.66 rows=41 width=4)
-> Seq Scan on chat_log (cost=0.00..3.00 rows=100 width=4)
-> Hash (cost=18.04..18.04 rows=18 width=36)
-> Subquery Scan csld (cost=17.63..18.04 rows=18 width=36)
-> HashAggregate (cost=17.63..17.86 rows=18 width=22665)
-> Hash Join (cost=15.63..17.54 rows=18 width=22665)
Hash Cond: (csl2.selection_id = us2.id)
-> Seq Scan on chat_selection_links csl2 (cost=0.00..1.56 rows=44 width=8)
Filter: (NOT deleted)
-> Hash (cost=15.41..15.41 rows=18 width=22665)
-> Seq Scan on user_selections us2 (cost=0.00..15.41 rows=18 width=22665)
Filter: (user_drawing AND deleted)
(50 rows)
-------------- next part --------------
explain SELECT tdesc.* FROM (
SELECT cl.id, cl.parent_id, cl.username, cl.type, cl.text, cl.time, cl.flags, cl.depth, '[stuff]' as extent, csls.selection, csld.drawing, round(date_part('epoch',time)*1000.0) AS epoch_ms, csls.selection, csld.drawing, ctu.target_users, ctg.target_groups, clr.replies, case when (num_target_users>0 or num_target_groups>0) then true else false end AS private FROM bv_mc2.chat_log AS cl
LEFT JOIN (
SELECT '[stuff]' AS selection, st_union(geom) AS ugeom, csl1.chat_id FROM bv_mc2.user_selections AS us1
INNER JOIN bv_mc2.chat_selection_links AS csl1 ON us1.id = csl1.selection_id WHERE (not us1.user_drawing and not csl1.deleted) GROUP BY chat_id
) AS csls ON csls.chat_id = cl.id
LEFT JOIN (
SELECT '[stuff]' AS drawing, st_union(geom) AS ugeom, csl2.chat_id FROM bv_mc2.user_selections AS us2
INNER JOIN bv_mc2.chat_selection_links AS csl2 ON us2.id = csl2.selection_id WHERE (us2.user_drawing and us2.deleted and not csl2.deleted) GROUP BY chat_id
) AS csld ON csld.chat_id = cl.id
LEFT JOIN (
SELECT chat_target_users.id, count(*) AS num_target_users, '[stuff]'::varchar AS target_users, ARRAY['stuff']::text[] AS target_users_array FROM bv_mc2.chat_target_users GROUP BY id
) AS ctu ON cl.id = ctu.id
LEFT JOIN (
SELECT chat_target_groups.id, count(*) AS num_target_groups, '[stuff]'::varchar AS target_groups, ARRAY['stuff']::text[] AS target_groups_array FROM bv_mc2.chat_target_groups GROUP BY id
) AS ctg ON cl.id = ctg.id
LEFT JOIN (
SELECT chat_log.parent_id AS id, '[stuff]' AS replies FROM bv_mc2.chat_log GROUP BY parent_id
) AS clr ON cl.id = clr.id WHERE ((num_target_users is null and num_target_groups is null) or (num_target_users+num_target_groups)=0 or ((cl.username = 'admin' or 'admin' = any(target_users_array) or ARRAY['admin','everyone'] && target_groups_array) and (time>'2010-03-20 00:52:57.230968-04'::timestamptz))) ORDER BY cl.id desc LIMIT 25
) AS tdesc ORDER BY tdesc.id asc;
QUERY PLAN
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Sort (cost=89.07..89.08 rows=1 width=385)
Sort Key: cl.id
-> Limit (cost=89.05..89.05 rows=1 width=229)
-> Sort (cost=89.05..89.05 rows=1 width=229)
Sort Key: cl.id
-> Nested Loop Left Join (cost=78.05..89.04 rows=1 width=229)
Join Filter: (csl1.chat_id = cl.id)
-> Nested Loop Left Join (cost=59.03..69.31 rows=1 width=197)
Join Filter: (cl.id = chat_log.parent_id)
-> Nested Loop Left Join (cost=55.78..64.72 rows=1 width=165)
Join Filter: (csl2.chat_id = cl.id)
-> Hash Left Join (cost=38.15..46.46 rows=1 width=133)
Hash Cond: (cl.id = ctu.id)
Filter: (((ctu.num_target_users IS NULL) AND (ctg.num_target_groups IS NULL)) OR ((ctu.num_target_users + ctg.num_target_groups) = 0) OR ((((cl.username)::text = 'admin'::text) OR ('admin'::text = ANY (ctu.target_users_array)) OR ('{admin,everyone}'::text[] && ctg.target_groups_array)) AND (cl."time" > '2010-03-20 00:52:57.230968-04'::timestamp with time zone)))
-> Hash Left Join (cost=35.45..42.20 rows=100 width=125)
Hash Cond: (cl.id = ctg.id)
-> Seq Scan on chat_log cl (cost=0.00..3.00 rows=100 width=53)
-> Hash (cost=32.95..32.95 rows=200 width=76)
-> Subquery Scan ctg (cost=28.45..32.95 rows=200 width=76)
-> HashAggregate (cost=28.45..30.95 rows=200 width=4)
-> Seq Scan on chat_target_groups (cost=0.00..22.30 rows=1230 width=4)
-> Hash (cost=2.27..2.27 rows=34 width=76)
-> Subquery Scan ctu (cost=1.51..2.27 rows=34 width=76)
-> HashAggregate (cost=1.51..1.94 rows=34 width=4)
-> Seq Scan on chat_target_users (cost=0.00..1.34 rows=34 width=4)
-> HashAggregate (cost=17.63..17.86 rows=18 width=22665)
-> Hash Join (cost=15.63..17.54 rows=18 width=22665)
Hash Cond: (csl2.selection_id = us2.id)
-> Seq Scan on chat_selection_links csl2 (cost=0.00..1.56 rows=44 width=8)
Filter: (NOT deleted)
-> Hash (cost=15.41..15.41 rows=18 width=22665)
-> Seq Scan on user_selections us2 (cost=0.00..15.41 rows=18 width=22665)
Filter: (user_drawing AND deleted)
-> HashAggregate (cost=3.25..3.66 rows=41 width=4)
-> Seq Scan on chat_log (cost=0.00..3.00 rows=100 width=4)
-> HashAggregate (cost=19.02..19.27 rows=20 width=22665)
-> Hash Join (cost=2.11..18.80 rows=44 width=22665)
Hash Cond: (us1.id = csl1.selection_id)
-> Seq Scan on user_selections us1 (cost=0.00..15.41 rows=223 width=22665)
Filter: (NOT user_drawing)
-> Hash (cost=1.56..1.56 rows=44 width=8)
-> Seq Scan on chat_selection_links csl1 (cost=0.00..1.56 rows=44 width=8)
Filter: (NOT deleted)
(43 rows)
More information about the postgis-users
mailing list