We have a dB with one table having about 1.3 million rows. We want to be
able to search subsets of the data. There are about 15 subsets and their
members are (more or less) fixed.
Assume we have the table 'main' with columns 'stockno' and 'descrip' and an
index on stockno. We create a subset table with a single column 'stockno',
populate it with:
insert into subset_table select stockno from main where <SOME CONDITION>;
and indexed it with
create index sub_idx on subset_table(stockno);
Now we want to select all entries in main that are in the subset also in
subset_table. So we do
select main.* from main m, subset_table s where m.stockno=s.stockno and
m.descrip ~ 'SEARCHTERM';
EXPLAIN show that main is first searched for SEARCHTERM then the results
filtered for s.stockno=m.stockno.
We would like it the other way round. I.e. only do the ~ match on descrip
for those rows in the subset.
How can we achieve this?
Thanks
Steve
Nested Loop (cost=317205.16 rows=3074115 width=184)
-> Nested Loop (cost=79304.37 rows=3 width=24)
-> Seq Scan on main m (cost=79300.27 rows=2 width=12)
-> Index Scan using sub_idx on subset_table s (cost=2.05
rows=203793 width=12)
-> Seq Scan on main (cost=79300.27 rows=1024705 width=160)
--
thorNET - Internet Consultancy, Services & Training
Phone: 01454 854413
Fax: 01454 854412
http://www.thornet.co.uk
