pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  

help / color / mirror / Atom feed
From: Ron Mayer <rm_pg@cheapcomplexdevices.com>
To: Ron Mayer <rm_pg@cheapcomplexdevices.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Subject: Re: TB-sized databases
Date: Thu, 06 Dec 2007 19:55:17 -0800
Message-ID: <4758C425.3080105@cheapcomplexdevices.com> (raw)
In-Reply-To: <4758A1B8.20501@cheapcomplexdevices.com>
References: <4544e0330711260904g45d893a8ub1731e245aa8fc84@mail.gmail.com>
	<13851.1196351131@sss.pgh.pa.us>
	<1196352866.4246.1304.camel@ebony.site>
	<200712051507.22745.xzilla@users.sourceforge.net>
	<1196933896.4255.340.camel@ebony.site>
	<20071206154212.GP5294@mathom.us>
	<15753.1196957598@sss.pgh.pa.us>
	<4758A1B8.20501@cheapcomplexdevices.com>

Tom Lane wrote:
> Ron Mayer <rm_pg@cheapcomplexdevices.com> writes:
>
>> Also shown below it seems that if I use "OFFSET 0" as a "hint"
>> I can force a much (10x) better plan.  I wonder if there's room for
>> a pgfoundry project for a patch set that lets us use more hints
>> than OFFSET 0.
>>
> There's something fishy about this --- given that that plan has a lower
> cost estimate, it should've picked it without any artificial
> constraints.


I think the reason it's not picking it was discussed back in this thread
too.
http://archives.postgresql.org/pgsql-performance/2005-03/msg00675.php
http://archives.postgresql.org/pgsql-performance/2005-03/msg00684.php
My offset 0 is forcing the outer join.
[Edit: Ugh - meant cartesian join - which helps this kind of query.]

>   What PG version are you using?

 logs=# select version();

 version
 ----------------------------------------------------------------------------------------------------------------
  PostgreSQL 8.2.1 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2 20061115 (prerelease) (Debian 4.1.1-21)
 (1 row)

> Do you perhaps have a low setting for join_collapse_limit?


 logs=# show join_collapse_limit;
  join_collapse_limit
 ---------------------
  8
 (1 row)

 Actually, IIRC back in that other thread,  "set join_collapse_limit =1;"
 helped
 http://archives.postgresql.org/pgsql-performance/2005-03/msg00663.php




view thread (61+ messages)  latest in thread

Message-ID: <4758C425.3080105@cheapcomplexdevices.com>
Permalink:  ../4758C425.3080105@cheapcomplexdevices.com/
Also on:    postgresql.org/message-id/4758C425.3080105@cheapcomplexdevices.com

 · 

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: rm_pg@cheapcomplexdevices.com, tgl@sss.pgh.pa.us
  Subject: Re: TB-sized databases
  In-Reply-To: <4758C425.3080105@cheapcomplexdevices.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox