Ron Arts <ron.arts@neonova.nl> writes:
> I have two tables A and B.
> Each table contains a non-unique indexed varchar column.
> Table A contains around 500000 rows
> table B contains around 4 million rows
> for a given value of col there are typically 3 records in A and
> 20 records in B (give or take)
> I want to do something like this:
> select a.somecol from A left join B on B.col = A.col where A.date > '2001-01-01';
> But this query runs for hours on an eight core server with 4G etc etc.
> I configured PG to use 128Mb shared memory, but fiddling with that value
> doesn't change much..
> And I continuously see the planner choosing for seq scans.
> I don't get it.
Given those numbers, index scans wouldn't help. You have not shown us
the EXPLAIN results, but I suspect that the best plan is a hash join,
or possibly a merge join. In either case the way to make it go faster
is to raise work_mem --- there is no other parameter that is likely
to change things much. You could probably profitably use work_mem up to
100MB or so on this problem. I would not recommend setting work_mem so
high as a global setting, but you can set it locally in the session
that's doing this join.
regards, tom lane