Date: Thu, 06 Dec 2007 10:42:14 -0500
From: Michael Stone <mstone+postgres@mathom.us>
Subject: Re: TB-sized databases
In-reply-to: <1196933896.4255.340.camel@ebony.site>
To: pgsql-performance@postgresql.org
Mail-followup-to: pgsql-performance@postgresql.org
Message-id: <20071206154212.GP5294@mathom.us>
MIME-version: 1.0
Content-type: text/plain; charset=us-ascii; format=flowed
Content-disposition: inline
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>
User-Agent: Mutt/1.5.13 (2006-08-11)

On Thu, Dec 06, 2007 at 09:38:16AM +0000, Simon Riggs wrote:
>The issue is that if somebody issues a "large query" then it will be a
>problem whichever plan the query takes. Forcing index scans can make a
>plan more expensive than a seq scan in many cases.

OTOH, the planner can really screw up queries on really large databases. 
IIRC, the planner can use things like unique constraints to get some 
idea, e.g., of how many rows will result from a join. Unfortunately, 
the planner can't apply those techniques to certain constructs common in 
really large db's (e.g., partitioned tables--how do you do a unique 
constraint on a partitioned table?) I've got some queries that the 
planner thinks will return on the order of 10^30 rows for that sort of 
reason. In practice, the query may return 10^3 rows, and the difference 
between the seq scan and the index scan is the difference between a 
query that takes a few seconds and a query that I will never run to 
completion. I know the goal would be to make the planner understand 
those queries better, but for now the answer seems to be to craft the 
queries very carefully and run explain first, making sure you see index 
scans in the right places.

Mike Stone