[SQL]

[Max Lipsky]

Hi All!

Why is too much difference in time execution between these two queries:


SELECT rc.id AS id, rc.name
FROM realisation_category rc
WHERE EXISTS (
SELECT * FROM realisation r, post p WHERE (r.realisation_category_id = rc.id AND r.site_id = 1)
OR (p.realisation_category_id = rc.id AND p.site_id = 1)
)
[2016-06-03 01:23:12] 35 row(s) retrieved starting from 1 in 14s 591ms (14s 612ms total)



SELECT rc.id AS id, rc.name
FROM realisation_category rc
WHERE EXISTS (
SELECT * FROM realisation r WHERE r.realisation_category_id = rc.id AND r.site_id = 1
) OR EXISTS (
SELECT * FROM post p WHERE p.realisation_category_id = rc.id AND p.site_id = 1
)
[2016-06-03 01:25:25] 35 row(s) retrieved starting from 1 in 64ms (86ms total)

Thanks

--
Sent via pgsql-sql mailing list (pgsq...@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[Steve Midgley]

In order to answer questions like that, generally, it's super helpful if you will include the "EXPLAIN" output for the query. It may also be useful to share some simple DDL stuff so we can create the tables and data you're using and try it out on our end.

In this specific case (without digging too much into this, and without the info above) I'd guess there's an index missing and probably on the field "realisation_category_id"

Steve

[Max Lipsky]

Thank you for your answer,

you can download test DB >> https://www.dropbox.com/s/uhcw3syyf5yfht0/test.zip?dl=0

Slow:

SELECT rc.id AS id, rc.name
FROM   realisation_category rc
WHERE  EXISTS (

    SELECT * FROM realisation r, post p WHERE (r.realisation_category_id = rc.id AND r.site_id = 1 AND rc.parent_id is not null)
                                              OR (p.realisation_category_id = rc.id AND p.site_id = 1 AND rc.parent_id is not null)
);

Nested Loop Semi Join  (cost=0.00..7099949.93 rows=5 width=520)

   Join Filter: (((r.realisation_category_id = rc.id) AND (r.site_id = 1)) OR ((p.realisation_category_id = rc.id) AND (p.site_id = 1)))

   ->  Seq Scan on realisation_category rc  (cost=0.00..2.44 rows=44 width=520)

         Filter: (parent_id IS NOT NULL)

   ->  Materialize  (cost=0.00..145886.48 rows=6511689 width=16)

         ->  Nested Loop  (cost=0.00..81532.04 rows=6511689 width=16)

               ->  Seq Scan on post p  (cost=0.00..78.81 rows=3681 width=8)

               ->  Materialize  (cost=0.00..61.53 rows=1769 width=8)

                     ->  Seq Scan on realisation r  (cost=0.00..52.69 rows=1769 width=8)

Fast:

SELECT rc.id AS id, rc.name
FROM   realisation_category rc
WHERE  EXISTS (

    SELECT * FROM realisation r WHERE r.realisation_category_id = rc.id AND r.site_id = 1 AND rc.parent_id is not null
) OR EXISTS (
    SELECT * FROM post p WHERE p.realisation_category_id = rc.id AND p.site_id = 1 AND rc.parent_id is not null
);

Seq Scan on realisation_category rc  (cost=0.00..1041.12 rows=33 width=520)

   Filter: ((SubPlan 1) OR (SubPlan 2))

   SubPlan 1

     ->  Result  (cost=0.00..61.53 rows=6 width=0)

           One-Time Filter: (rc.parent_id IS NOT NULL)

           ->  Seq Scan on realisation r  (cost=0.00..61.53 rows=6 width=0)

                 Filter: ((realisation_category_id = rc.id) AND (site_id = 1))

   SubPlan 2

     ->  Result  (cost=9.34..13.35 rows=1 width=0)

           One-Time Filter: (rc.parent_id IS NOT NULL)

           ->  Bitmap Heap Scan on post p  (cost=9.34..13.35 rows=1 width=0)

                 Recheck Cond: ((site_id = 1) AND (realisation_category_id = rc.id))

                 ->  BitmapAnd  (cost=9.34..9.34 rows=1 width=0)

                       ->  Bitmap Index Scan on idx_5a8a6c8df6bd1646  (cost=0.00..4.37 rows=12 width=0)

                             Index Cond: (site_id = 1)

                       ->  Bitmap Index Scan on idx_5a8a6c8dd4f97f1a  (cost=0.00..4.71 rows=58 width=0)

                             Index Cond: (realisation_category_id = rc.id)

[Max Lipsky]

Sorry, I send broken link

New link >> https://www.dropbox.com/s/8g3tkrikeap89q9/text.zip?dl=0

[Michael Moore]

Looks like a Cartesian join to me. SELECT * FROM realisation r, post p