Thread: Forcing index usage without 'enable_hashjoin = FALSE'
I'm using PostgreSQL 8.1.4 and I'm trying to force the planner to use an index. With the index, I get executions times of 0.5 seconds. Without, it's closer to 2.5 seconds. Compare these two sets of results (also provided at http://rafb.net/paste/results/ywcOZP66.html should it appear poorly formatted below): freshports.org=# \i test2.sql QUERY PLAN ---------------------------------------------------------------------- ---------------------------------------------------------------------- - Merge Join (cost=24030.39..24091.43 rows=3028 width=206) (actual time=301.301..355.261 rows=3149 loops=1) Merge Cond: ("outer".id = "inner".category_id) -> Sort (cost=11.17..11.41 rows=97 width=4) (actual time=0.954..1.300 rows=95 loops=1) Sort Key: c.id -> Seq Scan on categories c (cost=0.00..7.97 rows=97 width=4) (actual time=0.092..0.517 rows=97 loops=1) -> Sort (cost=24019.22..24026.79 rows=3028 width=206) (actual time=300.317..314.114 rows=3149 loops=1) Sort Key: p.category_id -> Nested Loop (cost=0.00..23844.14 rows=3028 width=206) (actual time=0.082..264.459 rows=3149 loops=1) -> Seq Scan on ports p (cost=0.00..6141.11 rows=3028 width=206) (actual time=0.026..133.575 rows=3149 loops=1) Filter: (status = 'D'::bpchar) -> Index Scan using element_pkey on element e (cost=0.00..5.83 rows=1 width=4) (actual time=0.022..0.026 rows=1 loops=3149) Index Cond: ("outer".element_id = e.id) Total runtime: 369.869 ms (13 rows) freshports.org=# set enable_hashjoin = true; SET freshports.org=# \i test2.sql QUERY PLAN ---------------------------------------------------------------------- ---------------------------------------------------------- Hash Join (cost=6156.90..13541.14 rows=3028 width=206) (actual time=154.741..2334.366 rows=3149 loops=1) Hash Cond: ("outer".category_id = "inner".id) -> Hash Join (cost=6148.68..13472.36 rows=3028 width=206) (actual time=153.801..2288.792 rows=3149 loops=1) Hash Cond: ("outer".id = "inner".element_id) -> Seq Scan on element e (cost=0.00..4766.70 rows=252670 width=4) (actual time=0.022..1062.626 rows=252670 loops=1) -> Hash (cost=6141.11..6141.11 rows=3028 width=206) (actual time=151.105..151.105 rows=3149 loops=1) -> Seq Scan on ports p (cost=0.00..6141.11 rows=3028 width=206) (actual time=0.027..131.072 rows=3149 loops=1) Filter: (status = 'D'::bpchar) -> Hash (cost=7.97..7.97 rows=97 width=4) (actual time=0.885..0.885 rows=97 loops=1) -> Seq Scan on categories c (cost=0.00..7.97 rows=97 width=4) (actual time=0.076..0.476 rows=97 loops=1) Total runtime: 2346.877 ms (11 rows) freshports.org=# Without leaving "enable_hashjoin = false", can you suggest a way to force the index usage? FYI, the query is: explain analyse SELECT P.id, P.category_id, P.version as version, P.revision as revision, P.element_id, P.maintainer, P.short_description, to_char(P.date_added - SystemTimeAdjust(), 'DD Mon YYYY HH24:MI:SS') as date_added, P.last_commit_id as last_change_log_id, P.package_exists, P.extract_suffix, P.homepage, P.status, P.broken, P.forbidden, P.ignore, P.restricted, P.deprecated, P.no_cdrom, P.expiration_date, P.latest_link FROM categories C, ports P JOIN element E on P.element_id = E.id WHERE P.status = 'D' AND P.category_id = C.id; -- Dan Langille : Software Developer looking for work my resume: http://www.freebsddiary.org/dan_langille.php
Dan Langille wrote: > I'm using PostgreSQL 8.1.4 and I'm trying to force the planner to use > an index. With the index, I get executions times of 0.5 seconds. > Without, it's closer to 2.5 seconds. > > Compare these two sets of results (also provided at > http://rafb.net/paste/results/ywcOZP66.html > should it appear poorly formatted below): > > freshports.org=# \i test2.sql > > QUERY PLAN > ---------------------------------------------------------------------- > ---------------------------------------------------------------------- > - > Merge Join (cost=24030.39..24091.43 rows=3028 width=206) (actual > time=301.301..355.261 rows=3149 loops=1) > Merge Cond: ("outer".id = "inner".category_id) > -> Sort (cost=11.17..11.41 rows=97 width=4) (actual > time=0.954..1.300 rows=95 loops=1) > Sort Key: c.id > -> Seq Scan on categories c (cost=0.00..7.97 rows=97 > width=4) (actual time=0.092..0.517 rows=97 loops=1) > -> Sort (cost=24019.22..24026.79 rows=3028 width=206) (actual > time=300.317..314.114 rows=3149 loops=1) > Sort Key: p.category_id > -> Nested Loop (cost=0.00..23844.14 rows=3028 width=206) > (actual time=0.082..264.459 rows=3149 loops=1) > -> Seq Scan on ports p (cost=0.00..6141.11 rows=3028 > width=206) (actual time=0.026..133.575 rows=3149 loops=1) > Filter: (status = 'D'::bpchar) > -> Index Scan using element_pkey on element e > (cost=0.00..5.83 rows=1 width=4) (actual time=0.022..0.026 rows=1 > loops=3149) > Index Cond: ("outer".element_id = e.id) > Total runtime: 369.869 ms > (13 rows) > > freshports.org=# set enable_hashjoin = true; > SET > freshports.org=# \i test2.sql > QUERY PLAN > ---------------------------------------------------------------------- > ---------------------------------------------------------- > Hash Join (cost=6156.90..13541.14 rows=3028 width=206) (actual > time=154.741..2334.366 rows=3149 loops=1) > Hash Cond: ("outer".category_id = "inner".id) > -> Hash Join (cost=6148.68..13472.36 rows=3028 width=206) > (actual time=153.801..2288.792 rows=3149 loops=1) > Hash Cond: ("outer".id = "inner".element_id) > -> Seq Scan on element e (cost=0.00..4766.70 rows=252670 > width=4) (actual time=0.022..1062.626 rows=252670 loops=1) > -> Hash (cost=6141.11..6141.11 rows=3028 width=206) > (actual time=151.105..151.105 rows=3149 loops=1) > -> Seq Scan on ports p (cost=0.00..6141.11 rows=3028 > width=206) (actual time=0.027..131.072 rows=3149 loops=1) > Filter: (status = 'D'::bpchar) > -> Hash (cost=7.97..7.97 rows=97 width=4) (actual > time=0.885..0.885 rows=97 loops=1) > -> Seq Scan on categories c (cost=0.00..7.97 rows=97 > width=4) (actual time=0.076..0.476 rows=97 loops=1) > Total runtime: 2346.877 ms > (11 rows) > > freshports.org=# > > Without leaving "enable_hashjoin = false", can you suggest a way to > force the index usage? > > FYI, the query is: > > explain analyse > SELECT P.id, > P.category_id, > P.version as version, > P.revision as revision, > P.element_id, > P.maintainer, > P.short_description, > to_char(P.date_added - SystemTimeAdjust(), 'DD Mon YYYY > HH24:MI:SS') as date_added, > P.last_commit_id as last_change_log_id, > P.package_exists, > P.extract_suffix, > P.homepage, > P.status, > P.broken, > P.forbidden, > P.ignore, > P.restricted, > P.deprecated, > P.no_cdrom, > P.expiration_date, > P.latest_link > FROM categories C, ports P JOIN element E on P.element_id = E.id > WHERE P.status = 'D' > AND P.category_id = C.id; > I doubt it would make a difference but if you: ... FROM categories C JOIN ports P on P.category_id=C.id JOIN element E on P.element_id = E.id WHERE P.status = 'D'; does it change anything? -- Postgresql & php tutorials http://www.designmagick.com/
On 23 Aug 2006 at 13:31, Chris wrote: > Dan Langille wrote: > > I'm using PostgreSQL 8.1.4 and I'm trying to force the planner to use > > an index. With the index, I get executions times of 0.5 seconds. > > Without, it's closer to 2.5 seconds. > > > > Compare these two sets of results (also provided at > > http://rafb.net/paste/results/ywcOZP66.html > > should it appear poorly formatted below): > > > > freshports.org=# \i test2.sql > > > > QUERY PLAN > > ---------------------------------------------------------------------- > > ---------------------------------------------------------------------- > > - > > Merge Join (cost=24030.39..24091.43 rows=3028 width=206) (actual > > time=301.301..355.261 rows=3149 loops=1) > > Merge Cond: ("outer".id = "inner".category_id) > > -> Sort (cost=11.17..11.41 rows=97 width=4) (actual > > time=0.954..1.300 rows=95 loops=1) > > Sort Key: c.id > > -> Seq Scan on categories c (cost=0.00..7.97 rows=97 > > width=4) (actual time=0.092..0.517 rows=97 loops=1) > > -> Sort (cost=24019.22..24026.79 rows=3028 width=206) (actual > > time=300.317..314.114 rows=3149 loops=1) > > Sort Key: p.category_id > > -> Nested Loop (cost=0.00..23844.14 rows=3028 width=206) > > (actual time=0.082..264.459 rows=3149 loops=1) > > -> Seq Scan on ports p (cost=0.00..6141.11 rows=3028 > > width=206) (actual time=0.026..133.575 rows=3149 loops=1) > > Filter: (status = 'D'::bpchar) > > -> Index Scan using element_pkey on element e > > (cost=0.00..5.83 rows=1 width=4) (actual time=0.022..0.026 rows=1 > > loops=3149) > > Index Cond: ("outer".element_id = e.id) > > Total runtime: 369.869 ms > > (13 rows) > > > > freshports.org=# set enable_hashjoin = true; > > SET > > freshports.org=# \i test2.sql > > QUERY PLAN > > ---------------------------------------------------------------------- > > ---------------------------------------------------------- > > Hash Join (cost=6156.90..13541.14 rows=3028 width=206) (actual > > time=154.741..2334.366 rows=3149 loops=1) > > Hash Cond: ("outer".category_id = "inner".id) > > -> Hash Join (cost=6148.68..13472.36 rows=3028 width=206) > > (actual time=153.801..2288.792 rows=3149 loops=1) > > Hash Cond: ("outer".id = "inner".element_id) > > -> Seq Scan on element e (cost=0.00..4766.70 rows=252670 > > width=4) (actual time=0.022..1062.626 rows=252670 loops=1) > > -> Hash (cost=6141.11..6141.11 rows=3028 width=206) > > (actual time=151.105..151.105 rows=3149 loops=1) > > -> Seq Scan on ports p (cost=0.00..6141.11 rows=3028 > > width=206) (actual time=0.027..131.072 rows=3149 loops=1) > > Filter: (status = 'D'::bpchar) > > -> Hash (cost=7.97..7.97 rows=97 width=4) (actual > > time=0.885..0.885 rows=97 loops=1) > > -> Seq Scan on categories c (cost=0.00..7.97 rows=97 > > width=4) (actual time=0.076..0.476 rows=97 loops=1) > > Total runtime: 2346.877 ms > > (11 rows) > > > > freshports.org=# > > > > Without leaving "enable_hashjoin = false", can you suggest a way to > > force the index usage? > > > > FYI, the query is: > > > > explain analyse > > SELECT P.id, > > P.category_id, > > P.version as version, > > P.revision as revision, > > P.element_id, > > P.maintainer, > > P.short_description, > > to_char(P.date_added - SystemTimeAdjust(), 'DD Mon YYYY > > HH24:MI:SS') as date_added, > > P.last_commit_id as last_change_log_id, > > P.package_exists, > > P.extract_suffix, > > P.homepage, > > P.status, > > P.broken, > > P.forbidden, > > P.ignore, > > P.restricted, > > P.deprecated, > > P.no_cdrom, > > P.expiration_date, > > P.latest_link > > FROM categories C, ports P JOIN element E on P.element_id = E.id > > WHERE P.status = 'D' > > AND P.category_id = C.id; > > > > I doubt it would make a difference but if you: > > ... > FROM categories C JOIN ports P on P.category_id=C.id JOIN element E on > P.element_id = E.id > WHERE P.status = 'D'; > > does it change anything? Not really, no: freshports.org=# \i test3.sql QUERY PLAN ---------------------------------------------------------------------- ---------------------------------------------------------------------- --- Hash Join (cost=5344.62..12740.73 rows=3365 width=204) (actual time=63.871..2164.880 rows=3149 loops=1) Hash Cond: ("outer".category_id = "inner".id) -> Hash Join (cost=5336.41..12665.22 rows=3365 width=204) (actual time=62.918..2122.529 rows=3149 loops=1) Hash Cond: ("outer".id = "inner".element_id) -> Seq Scan on element e (cost=0.00..4767.58 rows=252758 width=4) (actual time=0.019..1024.299 rows=252791 loops=1) -> Hash (cost=5328.00..5328.00 rows=3365 width=204) (actual time=60.228..60.228 rows=3149 loops=1) -> Bitmap Heap Scan on ports p (cost=34.02..5328.00 rows=3365 width=204) (actual time=1.900..41.316 rows=3149 loops=1) Recheck Cond: (status = 'D'::bpchar) -> Bitmap Index Scan on ports_deleted (cost=0.00..34.02 rows=3365 width=0) (actual time=1.454..1.454 rows=3149 loops=1) Index Cond: (status = 'D'::bpchar) -> Hash (cost=7.97..7.97 rows=97 width=4) (actual time=0.890..0.890 rows=97 loops=1) -> Seq Scan on categories c (cost=0.00..7.97 rows=97 width=4) (actual time=0.074..0.497 rows=97 loops=1) Total runtime: 2176.784 ms (13 rows) freshports.org=# -- Dan Langille : Software Developer looking for work my resume: http://www.freebsddiary.org/dan_langille.php
"Dan Langille" <dan@langille.org> writes:
> Without leaving "enable_hashjoin = false", can you suggest a way to
> force the index usage?
Have you tried reducing random_page_cost?
FYI, 8.2 should be a bit better about this.
regards, tom lane
On 23 Aug 2006 at 22:30, Tom Lane wrote: > "Dan Langille" <dan@langille.org> writes: > > Without leaving "enable_hashjoin = false", can you suggest a way to > > force the index usage? > > Have you tried reducing random_page_cost? Yes. No effect. > FYI, 8.2 should be a bit better about this. Good. This query is not critical, but it would be nice. Thank you. -- Dan Langille : Software Developer looking for work my resume: http://www.freebsddiary.org/dan_langille.php