Re: strange slow query performance - Mailing list pgsql-general

Ben Snaidero wrote:
> The following query runs as expected.
> 
> explain analyze SELECT MainTable.gid AS MainTable_gid,MainTable.datetime AS MainTable_datetime,MainTable.objectid AS
MainTable_objectid,MainTable.typeAS MainTable_type FROM MainTable  
 
>    WHERE objectid = ANY(ARRAY(SELECT NE.objectid AS entityId FROM ( nodeobject NE INNER JOIN (       Node N1NE INNER
JOINNode N2NE   ON ((N1NE.iLeft >= N2NE.iLeft) AND (N1NE.iLeft <= N2NE.iRight)))   ON NE.inodeid = N1NE.iId ) WHERE
N2NE.iId= ANY(ARRAY[14438,10814]))) 
 
>      AND objectid=3161;
>                                                                                QUERY PLAN
                                                  
 
>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Index Scan using ix_MainTable_objectid_datetime on MainTable  (cost=3254.91..3264.39 rows=1 width=32) (actual
time=33.094..33.094rows=0 loops=1)
 
>    Index Cond: ((objectid = ANY ($3)) AND (objectid = 3161))
>    InitPlan 1 (returns $3)
>      ->  Nested Loop  (cost=1.10..3254.75 rows=16296 width=4) (actual time=0.403..26.147 rows=19042 loops=1)
> [...]
>  Planning time: 5.693 ms
>  Execution time: 33.383 ms
> (15 rows)
> 
> But when we add a second condition to the where clause it causes the query performance to drop dramatically.  Values
in"any(array" do not make a difference.  In this example they are the same but even for different values performance is
stillthe poor.  Any ideas as to why this is happening?
 
> 
> explain analyze SELECT MainTable.gid AS MainTable_gid,MainTable.datetime AS MainTable_datetime,MainTable.objectid AS
MainTable_objectid,MainTable.typeAS MainTable_type FROM MainTable  
 
>    WHERE objectid = ANY(ARRAY(SELECT NE.objectid AS entityId FROM ( nodeobject NE INNER JOIN (       Node N1NE INNER
JOINNode N2NE   ON ((N1NE.iLeft >= N2NE.iLeft) AND (N1NE.iLeft <= N2NE.iRight)))   ON NE.inodeid = N1NE.iId ) WHERE
N2NE.iId= ANY(ARRAY[14438,10814]))) 
 
>      AND objectid = ANY(ARRAY(SELECT NE.objectid AS entityId FROM ( nodeobject NE INNER JOIN (       Node N1NE INNER
JOINNode N2NE   ON ((N1NE.iLeft >= N2NE.iLeft) AND (N1NE.iLeft <= N2NE.iRight)))   ON NE.inodeid = N1NE.iId ) WHERE
N2NE.iId= ANY(ARRAY[14438,10814]))) AND objectid=3161;
 
> 
>                                                                                 QUERY PLAN
                                                   
 
>  
>
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
>  Index Scan using ix_MainTable_objectid_datetime on MainTable  (cost=6509.66..6534.02 rows=1 width=32) (actual
time=16442.004..16442.004rows=0 loops=1)
 
>    Index Cond: ((objectid = ANY ($3)) AND (objectid = ANY ($7)) AND (objectid = 3161))
>    InitPlan 1 (returns $3)
>      ->  Nested Loop  (cost=1.10..3254.75 rows=16296 width=4) (actual time=0.438..28.484 rows=19042 loops=1)
> [...]
>    InitPlan 2 (returns $7)
>      ->  Nested Loop  (cost=1.10..3254.75 rows=16296 width=4) (actual time=0.056..11.786 rows=19042 loops=1)
> [...]
>  Planning time: 4.860 ms
>  Execution time: 16442.462 ms
> (26 rows)

Not sure what is going on, but why don't you put the condition "WHERE ne.objectid=3161"
into the subselects?  Then there should be at most one result row, and I can imagine
that things will become faster.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com