pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedForcing index usage without 'enable_hashjoin = FALSE'
5+ messages / 3 participants
[nested] [flat]
* Forcing index usage without 'enable_hashjoin = FALSE'
@ 2006-08-22 09:09 Dan Langille <dan@langille.org>
0 siblings, 2 replies; 5+ messages in thread
From: Dan Langille @ 2006-08-22 09:09 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Forcing index usage without 'enable_hashjoin = FALSE'
@ 2006-08-23 03:31 Chris <dmagick@gmail.com>
parent: Dan Langille <dan@langille.org>
1 sibling, 0 replies; 5+ messages in thread
From: Chris @ 2006-08-23 03:31 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: pgsql-performance
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/
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Forcing index usage without 'enable_hashjoin = FALSE'
@ 2006-08-24 02:11 Dan Langille <dan@langille.org>
parent: Dan Langille <dan@langille.org>
1 sibling, 2 replies; 5+ messages in thread
From: Dan Langille @ 2006-08-24 02:11 UTC (permalink / raw)
To: Chris <dmagick@gmail.com>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Forcing index usage without 'enable_hashjoin = FALSE'
@ 2006-08-24 02:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Dan Langille <dan@langille.org>
1 sibling, 0 replies; 5+ messages in thread
From: Tom Lane @ 2006-08-24 02:30 UTC (permalink / raw)
To: Dan Langille <dan@langille.org>; +Cc: Chris <dmagick@gmail.com>; pgsql-performance
"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
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: Forcing index usage without 'enable_hashjoin = FALSE'
@ 2006-08-24 02:42 Dan Langille <dan@langille.org>
parent: Dan Langille <dan@langille.org>
1 sibling, 0 replies; 5+ messages in thread
From: Dan Langille @ 2006-08-24 02:42 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Chris <dmagick@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2006-08-24 02:42 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2006-08-22 09:09 Forcing index usage without 'enable_hashjoin = FALSE' Dan Langille <dan@langille.org>
2006-08-23 03:31 ` Chris <dmagick@gmail.com>
2006-08-24 02:11 ` Dan Langille <dan@langille.org>
2006-08-24 02:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2006-08-24 02:42 ` Dan Langille <dan@langille.org>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox