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

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

From: Ron Arts <ron(dot)arts(at)neonova(dot)nl>
To: PostgreSQL List - Novice <pgsql-novice(at)postgresql(dot)org>
Subject: Re: Index not used in join.. (example included).
Date: 2009-09-18 14:27:32
Message-ID: 4AB398D4.4080308@neonova.nl (view raw or flat)
Thread:
Lists: pgsql-novice
Sorry. Maybe I should refrase my question.

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.

Ron

-- 
NeoNova BV
innovatieve internetoplossingen

http://www.neonova.nl  Science Park 140           1098 XG Amsterdam
info: 020-5611300      servicedesk: 020-5611302   fax: 020-5611301
KvK Amsterdam 34151241

Op dit bericht is de volgende disclaimer van toepassing:
http://www.neonova.nl/maildisclaimer

In response to

Responses

pgsql-novice by date

Next:From: Tom LaneDate: 2009-09-18 15:11:48
Subject: Re: Index not used in join.. (example included).
Previous:From: Tom LaneDate: 2009-09-18 14:10:50
Subject: Re: Index not used in join.. (example included)

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