Hello.
I need to make some sql-statement to be executed as fast as possible. :) My database
consists of:
1) table of categories having 1'000 rows, 2) table of manufacturers having 1'000 rows,
3) table of
resellers having 1'000 rows, 4) table of products having 1'000'000 rows. In the
products table there
are aproximately 1'000 products per category; as well as per manufacturer and per
reseller. Here
are these tables:
postgres=# create table categories ( c_id int4, c_name varchar(32) );
CREATE
postgres=# create table manufacturers ( m_id int4, m_name varchar(32) );
CREATE
postgres=# create table resellers ( r_id int4, r_name varchar(32) );
CREATE
postgres=# create table products ( p_id int4, c_id int4, m_id int4, r_id int4, p_name
varchar(32) );
CREATE
I want to get category identifiers (c_id) for those categories which have products
manufactured by
manufacturer with identifier (m_id) equal to 123 and reselled by companies which names
(r_name)
start with 'a'. In order to make this query faster I created two indicies:
postgres=# create index products_mcr on products ( m_id, c_id, r_id );
CREATE
postgres=# create index resellers_n on resellers ( r_name );
CREATE
I tried several sql-statements which can be used for my purpose, because they have
different
execution plans. They are listed below according with their plans. Each #a query is
different from
the corresponding # one in that it has fictive "order by m_id, c_id" clause. This
clause does not
change the result of a query, but in some way says the optimiser not to use "Sort"
step. It works for
the 2a (SubPlan), 3a (SubPlan) and 4a (Nested Loop) queries, but not for the 1a (Hash
Join).
And I want to use this query, because practice results show, that it is the fastest
one. At my
computer these queries took: 1a) 1sec, 2a) 16sec, 3a) 3sec, 4a) 3sec.
So, my question is: how can I get rid of this unnesesary "Sort" step in the execution
plan for hash join?
Thanks in advance.
P.S.: Excuse me for bad english.
+++
+++
1) select distinct c_id from products, ( select r_id from resellers where r_name like
'a%' ) as temp where m_id = 123 and products.r_id = temp.r_id;
Unique (cost=16.83..16.83 rows=1 width=12)
-> Sort (cost=16.83..16.83 rows=1 width=12)
-> Hash Join (cost=8.16..16.82 rows=1 width=12)
-> Index Scan using products_mcr on products (cost=0.00..8.14 rows=10
width=8)
-> Hash (cost=8.14..8.14 rows=10 width=4)
-> Index Scan using resellers_n on resellers (cost=0.00..8.14
rows=10 width=4)
1a) select distinct c_id, m_id from products, ( select r_id from resellers where
r_name like 'a%' ) as temp where m_id = 123 and products.r_id = temp.r_id order by
m_id, c_id;
Unique (cost=16.83..16.83 rows=1 width=12)
-> Sort (cost=16.83..16.83 rows=1 width=12)
-> Hash Join (cost=8.16..16.82 rows=1 width=12)
-> Index Scan using products_mcr on products (cost=0.00..8.14 rows=10
width=8)
-> Hash (cost=8.14..8.14 rows=10 width=4)
-> Index Scan using resellers_n on resellers (cost=0.00..8.14
rows=10 width=4)
2) select distinct c_id from products where m_id = 123 and r_id in ( select r_id from
resellers where r_name like 'a%' );
Unique (cost=89.68..89.71 rows=1 width=4)
-> Sort (cost=89.68..89.68 rows=10 width=4)
-> Index Scan using products_mcr on products (cost=0.00..89.52 rows=10
width=4)
SubPlan
-> Materialize (cost=8.14..8.14 rows=10 width=4)
-> Index Scan using resellers_n on resellers (cost=0.00..8.14
rows=10 width=4)
2a) select distinct c_id, m_id from products where m_id = 123 and r_id in ( select
r_id from resellers where r_name like 'a%' ) order by m_id, c_id;
Unique (cost=0.00..89.57 rows=1 width=8)
-> Index Scan using products_mcr on products (cost=0.00..89.52 rows=10 width=8)
SubPlan
-> Materialize (cost=8.14..8.14 rows=10 width=4)
-> Index Scan using resellers_n on resellers (cost=0.00..8.14
rows=10 width=4)
3) select distinct c_id from products where m_id = 123 and exists ( select r_id from
resellers where r_name like 'a%' and r_id = products.r_id );
Unique (cost=89.91..89.93 rows=1 width=4)
-> Sort (cost=89.91..89.91 rows=10 width=4)
-> Index Scan using products_mcr on products (cost=0.00..89.74 rows=10
width=4)
SubPlan
-> Index Scan using resellers_n on resellers (cost=0.00..8.16 rows=1
width=4)
3a) select distinct c_id, m_id from products where m_id = 123 and exists ( select r_id
from resellers where r_name like 'a%' and r_id = products.r_id ) order by m_id, c_id;
Unique (cost=0.00..89.79 rows=1 width=8)
-> Index Scan using products_mcr on products (cost=0.00..89.74 rows=10 width=8)
SubPlan
-> Index Scan using resellers_n on resellers (cost=0.00..8.16 rows=1
width=4)
4) set enable_hashjoin = off; set enable_mergejoin = off; select distinct c_id from
products, ( select r_id from resellers where r_name like 'a%' ) as temp where m_id =
123 and products.r_id = temp.r_id;
Unique (cost=90.75..90.76 rows=1 width=12)
-> Sort (cost=90.75..90.75 rows=1 width=12)
-> Nested Loop (cost=0.00..90.74 rows=1 width=12)
-> Index Scan using products_mcr on products (cost=0.00..8.14 rows=10
width=8)
-> Index Scan using resellers_n on resellers (cost=0.00..8.14 rows=10
width=4)
4a) set enable_hashjoin = off; set enable_mergejoin = off; select distinct c_id, m_id
from products, ( select r_id from resellers where r_name like 'a%' ) as temp where
m_id = 123 and products.r_id = temp.r_id order by m_id, c_id;
Unique (cost=0.00..90.75 rows=1 width=16)
-> Nested Loop (cost=0.00..90.74 rows=1 width=16)
-> Index Scan using products_mcr on products (cost=0.00..8.14 rows=10
width=12)
-> Index Scan using resellers_n on resellers (cost=0.00..8.14 rows=10
width=4)
--
WBR, Alexey Nalbat
---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
