Skip site navigation (1) Skip section navigation (2)

Re: Index not used in join.. (example included).

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Ron Arts <ron(dot)arts(at)neonova(dot)nl>
Cc: PostgreSQL List - Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Index not used in join.. (example included).
Date: 2009-09-18 15:11:48
Message-ID: 24647.1253286708@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-novice
Ron Arts <ron(dot)arts(at)neonova(dot)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

In response to

pgsql-novice by date

Next:From: Frank GlandorfDate: 2009-09-19 02:13:36
Subject: xmlns in xpath query
Previous:From: Ron ArtsDate: 2009-09-18 14:27:32
Subject: Re: Index not used in join.. (example included).

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group