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

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

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

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).


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 =
                                         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);

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to