"Huan Ruan" <huan.ruan...@gmail.com> wrote: > explain (analyze, buffers) > SELECT > * > FROM IM_Match_Table smalltable > inner join invtran bigtable on bigtable.invtranref = smalltable.invtranref
Well, one table or the other will need to be read in full, and you would normally want that one to be the small table. When there is no ORDER BY clause, the fastest way to do that will normally be a seqscan. So that part of the query is as it should be. The only question is whether the access to the big table is using the fastest technique. If you want to see what the planner's second choice would have been, you could run: SET enable_indexscan = off; on a connection and try the explain again. If you don't like that one, you might be able to disable another node type and see what you get. If one of the other alternatives is faster, that would suggest that adjustments are needed to the costing factors; otherwise, it just takes that long to read hundreds of thousands of rows in one table and look for related data for each of them in another table. > "Nested Loop (cost=0.00..341698.92 rows=48261 width=171) (actual > time=0.042..567.980 rows=48257 loops=1)" Frankly, at 12 microseconds per matched pair of rows, I think you're doing OK. -Kevin -- Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
