Re: Predicate information in EXPLAIN Command

On 14.05.2013 12:23, Sameer Thakur wrote:
> Hello,
> I am trying to find predicate information for a given SQL query plan as
> provided by Oracle using DBMS_XPLAN. I am looking at the EXPLAIN command
> for getting this query plan information, with no luck so far.
>
> Does the EXPLAIN command provide predicate information?

Sure. For example,

postgres=# explain select * from a where id = 123;
                     QUERY PLAN
---------------------------------------------------
  Seq Scan on a  (cost=0.00..40.00 rows=12 width=4)
    Filter: (id = 123)
(2 rows)

The predicate is right there on the Filter line. Likewise for a join:

postgres=# explain select * from a, b where a.id = b.id;
                            QUERY PLAN
-----------------------------------------------------------------
  Hash Join  (cost=64.00..134.00 rows=2400 width=8)
    Hash Cond: (a.id = b.id)
    ->  Seq Scan on a  (cost=0.00..34.00 rows=2400 width=4)
    ->  Hash  (cost=34.00..34.00 rows=2400 width=4)
          ->  Seq Scan on b  (cost=0.00..34.00 rows=2400 width=4)
(5 rows)

The join predicate is on the Hash Cond line.

- Heikki