Re: how to improve perf of 131MM row table?

On 06/26/2014 09:22 AM, AJ Weber wrote:

> I sent the details as identified by pgAdmin III.

Interesting. Either there is a bug in pgAdmin, or you're connecting to a
different database that is missing the primary key. What is the EXPLAIN
ANALYZE output if you execute the query you sent on a psql prompt?

> "alf_node_pkey" PRIMARY KEY, btree (id) CLUSTER
> would indicate to me that there is a PK on alf_node table, it is on
> column "id", it is of type btree, and the table is clustered around that
> index.
>
> Am I reading this totally wrong?

No, that's right. But that wasn't in the SQL you sent. In fact, there's
a lot of stuff missing in that output.

Try running the EXPLAIN ANALYZE using the same psql connection you used
to retrieve the actual table structure just now. I suspect you've
accidentally connected to the wrong database. If it's still doing the
sequence scan, we'll have to dig deeper.

--
Shaun Thomas
OptionsHouse, LLC | 141 W. Jackson Blvd. | Suite 800 | Chicago IL, 60604
312-676-8870
sthomas(at)optionshouse(dot)com

______________________________________________

See http://www.peak6.com/email_disclaimer/ for terms and conditions related to this email