Re: How does PostgerSQL planner decide driving table

El 06/10/14 02:38, Shingo horiuchi escribió:
>
> Dear All,
>
>
>
> I have read about query tuning and attempt to check the impact of
>
> exchanging the driving table in Join condition.
>
>
>
> To test Simple Join condition, I prepared Two tables.
>
> One is the table for employees and another is the table for departments.
>
> Employees table has the foreign key which referencing departments table.
>
>
>
> The proportion of each table, filtered table and joined table is below:
>
> (E means employees table and D means departments table.)
>
> #rows in E #rows in D #filtered rows in
> E #filtered rows in D #rows in E
> and D
>
> 10000 490
> 1000
> 245 9800
>
>
>
> After make the index on the filtered column, I tried the query:
>
> EXPLAIN ANALYZE
>
> SELECT D.Department_Name, E.Last_Name, E.First_Name
>
> FROM Employees E, Departments D
>
> WHERE E.Department_Id=D.Department_Id
>
> AND E.Exempt_Flag='Y'
>
> AND D.US_Based_Flag='Y'
>
> ;
>
>
>
> Result was:
>
> Hash Join (cost=8.85..241.59 rows=499 width=15) (actual
> time=0.105..2.052 rows=518 loops=1)
>
> Hash Cond: (e.department_id = d.department_id)
>
> -> Seq Scan on employees e (cost=0.00..209.00 rows=5000 width=17)
> (actual time=0.007..1.541 rows=5000 loops=1)
>
> Filter: (exempt_flag = 'Y'::bpchar)
>
> Rows Removed by Filter: 5000
>
> -> Hash (cost=8.24..8.24 rows=49 width=14) (actual
> time=0.087..0.087 rows=49 loops=1)
>
> Buckets: 1024 Batches: 1 Memory Usage: 3kB
>
> -> Bitmap Heap Scan on departments d (cost=4.63..8.24
> rows=49 width=14) (actual time=0.069..0.078 rows=49 loops=1)
>
> Recheck Cond: (us_based_flag = 'Y'::bpchar)
>
> -> Bitmap Index Scan on dept2_flg_idx
> (cost=0.00..4.62 rows=49 width=0) (actual time=0.063..0.063 rows=49
> loops=1)
>
> Index Cond: (us_based_flag = 'Y'::bpchar)
>
> Total runtime: 2.095 ms
>

I would recommend to run an ANALYZE on both tables.

You can't exchange the seqscan table on that query due that it needs to
read ALL the
records on E table. If you seqscan D first, the engine needs to read
again all the E
records.

If you want all the employees across all departments, you'll always end
up reading all
the employees.

What you can do to other plans, is disabling enable_hashjoin.

> */ /*
>
> In order to exchange the driving table, I tried the query:
>
> EXPLAIN ANALYZE
>
> SELECT D.Department_Name, E.Last_Name, E.First_Name
>
> FROM Departments D, Employees E
>
> WHERE D.Department_Id=E.Department_Id
>
> AND E.Exempt_Flag='Y'
>
> AND D.US_Based_Flag='Y'
>
> ;
>
>
>
> However, the result was same.
>
> I think this is because the query planner can optimizer the 2^nd
> query based on table statistics of E and D.
>
> E being the larger number of records and has higher filtering rate so
> it continues to be driving table.
>
>
>
> Now, I tried another test case to confirm my assumption.
>
> The proportion of another test case is different from above one.
>
> The proportion of each table, filtered table and joined table is below:
>
> (E means employees table and D means departments table.)
>
> #rows in E #rows in D #filtered rows in
> E #filtered rows in D #rows in E
> and D
>
> 10000
> 490 5000
> 49 9800
>
> The important point is the difference in the filtering rate.
>
> In this case, departments table is higher filtering rate,
>
> so taking departments table as driving table will be able to cut the
> computational cost, I think.
>
>
>
> I tried same query:
>
> EXPLAIN ANALYZE
>
> SELECT D.Department_Name, E.Last_Name, E.First_Name
>
> FROM Employees E, Departments D
>
> WHERE E.Department_Id=D.Department_Id
>
> AND E.Exempt_Flag='Y'
>
> AND D.US_Based_Flag='Y'
>
> And
>
> EXPLAIN ANALYZE
>
> SELECT D.Department_Name, E.Last_Name, E.First_Name
>
> FROM Employees E, Departments D
>
> WHERE E.Department_Id=D.Department_Id
>
> AND E.Exempt_Flag='Y'
>
> AND D.US_Based_Flag='Y'
>
>
>

Both queries are identical (?).

--
--
Emanuel Calvo http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services