Re: slow sub-query problem

In short...

tablea as t1 join tablea as t2 on t1.id = t2.id

A natural key prevents duplicate real data which a serially generated made up key does not.

David J.

On Monday, November 17, 2014, Tim Dudgeon <tdudgeon.ml@gmail.com> wrote:

On 17/11/2014 18:44, David G Johnston wrote:

Tim Dudgeon wrote

All relevant columns are indexed and using PostgreSQL 9.4.
Any clues how to re-write it to avoid the slow sub-query.

Try using an actual join instead of a subquery.  You will have to provide
aliases and then setup the where clause appropriately.

I'm trying to go in that direction but in the query is entirely within one table, so I need to join the table to itself? I've been trying this but not getting it to work yet.

I am reading the query correctly in that the repeated reference to 643413 is
redundant?

In this example its sort of redundant, but in a real world case the query for structure_id and property_id are independent and may have nothing in common.

The lack of a defined natural primary key makes blind reasoning
difficult.

The id column is the primary key.

Tim

David J.





--
View this message in context: http://postgresql.nabble.com/slow-sub-query-problem-tp5827273p5827275.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.