Re: slow sub-query problem

Please reply to the list...

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(dot)ml(at)gmail(dot)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.
>>
>>
>>
>