Re: Slow query with sub-select

From: Tom Lane
Subject: Re: Slow query with sub-select
Date: ,
Msg-id: 27349.1310831902@sss.pgh.pa.us
(view: Whole thread, Raw)
In response to: Re: Slow query with sub-select  (- -)
Responses: Re: Slow query with sub-select  (- -)
List: pgsql-general

Tree view

Slow query with sub-select  (- -, )
 Re: Slow query with sub-select  (David Johnston, )
  Re: Slow query with sub-select  (- -, )
   Re: Slow query with sub-select  (Tom Lane, )
    Re: Slow query with sub-select  (- -, )
     Re: Slow query with sub-select  (Rick Genter, )
   Re: Slow query with sub-select  (Michael Nolan, )

- - <> writes:
> The weird thing is that before I updated my server the query was about 5 times faster.
> I've googled and I think the problem lies with the under-estimation of the query planner about the number of rows in
thenested table.I will be trying the 'set enable_seqscan = false' solution to see if that'll improve.
        

You evidently already do have that turned off.  I'd suggest reverting
that change (ie, allow seqscan) and instead increase work_mem enough
so that the hash join can work without spilling to disk.  This query
is a perfect example of where indexes do not help, and trying to force
them to be used makes things slower not faster.

            regards, tom lane


pgsql-general by date:

From: Michael Nolan
Date:
Subject: Re: Slow query with sub-select
From: cesitarps
Date:
Subject: pass XML as an argument and INSERT (key/value pairs) in postgresql