X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 9B3543A4F6C for ; Fri, 7 Jan 2005 19:18:08 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 50417-03 for ; Fri, 7 Jan 2005 19:17:46 +0000 (GMT) Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.206]) by svr1.postgresql.org (Postfix) with ESMTP id B0A8C3A4AD9 for ; Fri, 7 Jan 2005 19:17:32 +0000 (GMT) Received: by wproxy.gmail.com with SMTP id 71so1938116wra for ; Fri, 07 Jan 2005 11:17:32 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:reply-to:to:subject:in-reply-to:mime-version:content-type:content-transfer-encoding:references; b=GoGLrpTUoKKUhRKh+JMZ4Tm01mhWSP5N2I5qrrqpqa3i/oMVbXusTZhMz3cqYTeusEF/MZOVPrnlPE5L3GNgQ9d04cgRqI94Odp4/AGg2EpCY0v1pobOAQYUOBwfGm7V9OEqMarWq4F74uZ7AiHAX+Kd3kDivTdzi4mJqKFSLnY= Received: by 10.54.13.33 with SMTP id 33mr183885wrm; Fri, 07 Jan 2005 11:17:31 -0800 (PST) Received: by 10.54.13.17 with HTTP; Fri, 7 Jan 2005 11:17:31 -0800 (PST) Message-ID: Date: Fri, 7 Jan 2005 14:17:31 -0500 From: David Jaquay Reply-To: David Jaquay To: pgsql-performance@postgresql.org Subject: Query across a date range In-Reply-To: <20050107182106.6CBEC5AF685@svr4.postgresql.org> Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit References: <20050107182106.6CBEC5AF685@svr4.postgresql.org> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.024 tagged_above=0 required=5 tests=RCVD_BY_IP X-Spam-Level: X-Archive-Number: 200501/78 X-Sequence-Number: 9768 Summary: Doing a two or three table join for a date range performs worse than doing the same query individually for each date in the range. What works: Doing a query just on a single date or a date range (against just one table) runs quick; 'explain' says it uses an index scan. Doing a query on a single date for one store or for one market uses all index scans, and runs quick as well. The problem: Doing a query for a date range on a particular store or market, though, for a date range of more than a few days does a sequential scan of sales_tickets, and performs worse than doing one single date query for each date. My 'explain' for one such query is below. Background: I have two or three tables involved in a query. One table is holds stores (7 rows at present), one holds sales tickets (about 5 million) and one holds line items (about 10 million). It's test data that I've generated and loaded using '\copy from'. Each has a primary key, and line items have two dates, written and delivered, that are indexed individually. Store has a market id; a market contains multiple stores (in my case, 2 or 3). Each sales ticket has 1-3 line items. Is there a way to tell postgres to use an index scan on sales_tickets? Curiously, in response to recent postings in the "Low Performance for big hospital server" thread, when I flatten the tables by putting storeid into line_items, it runs somewhat faster in all cases, and much faster in some; (I have times, if anyone is interested). Thanks, Dave mydb=> explain select * from line_items t, sales_tickets s where writtenDate >= '12/01/2002' and writtenDate <= '12/31/2002' and t.ticketId = s.ticketId and s.storeId = 1; QUERY PLAN --------------------------------------------------------------------------------------------------- Hash Join (cost=93865.46..114054.74 rows=19898 width=28) Hash Cond: ("outer".ticketId = "inner".ticketId) -> Index Scan using line_items_written on line_items t (cost=0.00..3823.11 rows=158757 width=16) Index Cond: ((writtendate >= '2002-12-01'::date) AND (writtendate <= '2002-12-31'::date)) -> Hash (cost=89543.50..89543.50 rows=626783 width=12) -> Seq Scan on sales_tickets s (cost=0.00..89543.50 rows=626783 width=12) Filter: (storeid = 1) (7 rows) mydb=> explain select * from line_items t, sales_tickets s where writtenDate = '12/01/2002' and t.ticketId = s.ticketId and s.storeid = 1; QUERY PLAN --------------------------------------------------------------------------------------------- Nested Loop (cost=0.00..16942.25 rows=697 width=28) -> Index Scan using line_items_written on soldtrx t (cost=0.00..121.97 rows=5554 width=16) Index Cond: (writtendate = '2002-12-01'::date) -> Index Scan using sales_tickets_pkey on sales_tickets s (cost=0.00..3.02 rows=1 width=12) Index Cond: ("outer".ticketId = s.ticketId) Filter: (storeid = 1) (6 rows) The tables: create table stores -- 7 rows ( storeId integer not null, marketId integer not null ); create table sales_tickets -- 500,000 rows ( ticketId integer primary key, storeId integer not null, custId integer not null ); create table line_items -- 1,000,000 rows ( lineItemId integer primary key, ticketId integer references sales_tickets, writtenDate date not null, deliveredDate date not null ); create index line_items_written on line_items (writtenDate); create index line_items_delivered on line_items (deliveredDate);