Message-ID: <52A9F2A2.4080901@gmail.com>
Date: Thu, 12 Dec 2013 12:30:10 -0500
From: Sev Zaslavsky <sevzas@gmail.com>
User-Agent: Mozilla/5.0 (Windows NT 6.0; WOW64;
 rv:24.0) Gecko/20100101 Thunderbird/24.1.0
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: slow query - will CLUSTER help?
Content-Type: multipart/alternative;
 boundary="------------090504030502020704000500"
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

This is a multi-part message in MIME format.
--------------090504030502020704000500
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hello,_
_
I've got a very simple table with a very simple SELECT query, but it 
takes longer on the initial run than I'd like, so I want to see if there 
is a strategy to optimize this.

Table rt_h_nbbo contains several hundred million rows.  All rows for a 
given entry_date are appended to this table in an overnight process 
every night - on the order of several million rows per day.

The objective is to select all of the rows for a given product_id on a 
given entry_date.

There is a b-tree index on (product_id, entry_date). The index appears 
to be used correctly.  I'm seeing that if the data pages are not in 
memory, nearly all of the time is spent on disk I/O.  The first time, 
the query takes 21 sec.  If I run this query a second time, it completes 
in approx 1-2 ms.

I perceive an inefficiency here and I'd like your input as to how to 
deal with it: The end result of the query is 1631 rows which is on the 
order of about a couple hundred Kb of data.  Compare that to the amount 
of I/O that was done: 1634 buffers were loaded, 16Mb per page - that's 
about 24 Gb of data!  Query completed in 21 sec.  I'd like to be able to 
physically re-organize the data on disk so that the data for a given 
product_id on a entry_date is concentrated on a few pages instead of 
being scattered like I see here.

_First question_ is: Does loading 24Gb of data in 21 sec seem "about 
right" (hardware specs at bottom of email)?

_Second question_: Is it possible to tell postgres to physically store 
the data in such a way that it parallels an index? I recall you can do 
this in Sybase with a CLUSTERED index.  The answer for Postgresql seems 
to be "yes, use the CLUSTER command". But this command does a one-time 
clustering and requires periodic re-clustering.  Is this the best 
approach?  Are there considerations with respect to the type of index 
(B-tree, GIST, SP-GIST) being used for CLUSTER ?

Thanks

-Sev


_Table (this is a fairly large table - hundreds of millions of rows):_

CREATE TABLE rt_h_nbbo
(
   product_id integer NOT NULL,
   bid_price double precision NOT NULL DEFAULT 0.0,
   bid_size integer NOT NULL DEFAULT 0,
   ask_price double precision NOT NULL DEFAULT 0.0,
   ask_size integer NOT NULL DEFAULT 0,
   last_price double precision NOT NULL DEFAULT 0.0,
   entry_date date NOT NULL,
   entry_time time without time zone NOT NULL,
   event_time time without time zone NOT NULL,
   day_volume bigint NOT NULL DEFAULT 0,
   day_trade_ct integer,
   entry_id bigint NOT NULL,
   CONSTRAINT rt_h_nbbo_pkey PRIMARY KEY (entry_id),
   CONSTRAINT rt_h_nbbo_pfkey FOREIGN KEY (product_id)
       REFERENCES product (product_id) MATCH SIMPLE
       ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
   OIDS=FALSE
);
ALTER TABLE rt_h_nbbo
   OWNER TO postgres;

_Index_:

CREATE INDEX rt_h_nbbo_idx
   ON rt_h_nbbo
   USING btree
   (product_id, entry_date DESC);

_Test_:

SET track_io_timing = on;
EXPLAIN (ANALYZE,BUFFERS,VERBOSE,COSTS,TIMING) select * from rt_h_nbbo 
where product_id=6508 and entry_date='2013-11-26';

_Output_:

"Index Scan using rt_h_nbbo_idx on public.rt_h_nbbo (cost=0.00..12768.21 
rows=3165 width=76) (actual time=12.549..21654.547 rows=1631 loops=1)"
"  Output: product_id, bid_price, bid_size, ask_price, ask_size, 
last_price, entry_date, entry_time, event_time, day_volume, 
day_trade_ct, entry_id"
"  Index Cond: ((rt_h_nbbo.product_id = 6508) AND (rt_h_nbbo.entry_date 
= '2013-11-26'::date))"
"  Buffers: shared hit=4 read=1634"
"  I/O Timings: read=21645.468"
"Total runtime: 21655.002 ms"

_Hardware_

Top of the line HP DL380 G7 server with 32 Gb Ram,  P410i RAID, 10K SAS 
drives in Raid-1 config.  Wal on separate Raid-1 volume with 15K SAS drives.
The only unusual thing here is that I'm running on Windows Server 2008 R2.

--------------090504030502020704000500
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=ISO-8859-1">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    Hello,<u><br>
    </u><br>
    I've got a very simple table with a very simple SELECT query, but it
    takes longer on the initial run than I'd like, so I want to see if
    there is a strategy to optimize this.<br>
    <br>
    Table rt_h_nbbo contains several hundred million rows.&nbsp; All rows for
    a given entry_date are appended to this table in an overnight
    process every night - on the order of several million rows per day.<br>
    <br>
    The objective is to select all of the rows for a given product_id on
    a given entry_date.<br>
    <br>
    There is a b-tree index on (product_id, entry_date). The index
    appears to be used correctly.&nbsp; I'm seeing that if the data pages are
    not in memory, nearly all of the time is spent on disk I/O.&nbsp; The
    first time, the query takes 21 sec.&nbsp; If I run this query a second
    time, it completes in approx 1-2 ms.<br>
    <br>
    I perceive an inefficiency here and I'd like your input as to how to
    deal with it: The end result of the query is 1631 rows which is on
    the order of about a couple hundred Kb of data.&nbsp; Compare that to the
    amount of I/O that was done: 1634 buffers were loaded, 16Mb per page
    - that's about 24 Gb of data!&nbsp; Query completed in 21 sec.&nbsp; I'd like
    to be able to physically re-organize the data on disk so that the
    data for a given product_id on a entry_date is concentrated on a few
    pages instead of being scattered like I see here. <br>
    <br>
    <u>First question</u> is: Does loading 24Gb of data in 21 sec seem
    "about right" (hardware specs at bottom of email)?<br>
    <br>
    <u>Second question</u>: Is it possible to tell postgres to
    physically store the data in such a way that it parallels an index?&nbsp;
    I recall you can do this in Sybase with a CLUSTERED index.&nbsp; The
    answer for Postgresql seems to be "yes, use the CLUSTER command".&nbsp;
    But this command does a one-time clustering and requires periodic
    re-clustering.&nbsp; Is this the best approach?&nbsp; Are there considerations
    with respect to the type of index (B-tree, GIST, SP-GIST) being used
    for CLUSTER ?<br>
    <br>
    Thanks<br>
    <br>
    -Sev<br>
    <br>
    <br>
    <u>Table (this is a fairly large table - hundreds of millions of
      rows):</u><br>
    <br>
    CREATE TABLE rt_h_nbbo<br>
    (<br>
    &nbsp; product_id integer NOT NULL,<br>
    &nbsp; bid_price double precision NOT NULL DEFAULT 0.0,<br>
    &nbsp; bid_size integer NOT NULL DEFAULT 0,<br>
    &nbsp; ask_price double precision NOT NULL DEFAULT 0.0,<br>
    &nbsp; ask_size integer NOT NULL DEFAULT 0,<br>
    &nbsp; last_price double precision NOT NULL DEFAULT 0.0,<br>
    &nbsp; entry_date date NOT NULL,<br>
    &nbsp; entry_time time without time zone NOT NULL,<br>
    &nbsp; event_time time without time zone NOT NULL,<br>
    &nbsp; day_volume bigint NOT NULL DEFAULT 0,<br>
    &nbsp; day_trade_ct integer,<br>
    &nbsp; entry_id bigint NOT NULL,<br>
    &nbsp; CONSTRAINT rt_h_nbbo_pkey PRIMARY KEY (entry_id),<br>
    &nbsp; CONSTRAINT rt_h_nbbo_pfkey FOREIGN KEY (product_id)<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; REFERENCES product (product_id) MATCH SIMPLE<br>
    &nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ON UPDATE NO ACTION ON DELETE NO ACTION<br>
    )<br>
    WITH (<br>
    &nbsp; OIDS=FALSE<br>
    );<br>
    ALTER TABLE rt_h_nbbo<br>
    &nbsp; OWNER TO postgres;<br>
    <br>
    <u>Index</u>:<br>
    <br>
    CREATE INDEX rt_h_nbbo_idx<br>
    &nbsp; ON rt_h_nbbo<br>
    &nbsp; USING btree<br>
    &nbsp; (product_id, entry_date DESC);<br>
    <br>
    <u>Test</u>:<br>
    <br>
    SET track_io_timing = on; <br>
    EXPLAIN (ANALYZE,BUFFERS,VERBOSE,COSTS,TIMING) select * from
    rt_h_nbbo where product_id=6508 and entry_date='2013-11-26';<br>
    <br>
    <u>Output</u>:<br>
    <br>
    "Index Scan using rt_h_nbbo_idx on public.rt_h_nbbo&nbsp;
    (cost=0.00..12768.21 rows=3165 width=76) (actual
    time=12.549..21654.547 rows=1631 loops=1)"<br>
    "&nbsp; Output: product_id, bid_price, bid_size, ask_price, ask_size,
    last_price, entry_date, entry_time, event_time, day_volume,
    day_trade_ct, entry_id"<br>
    "&nbsp; Index Cond: ((rt_h_nbbo.product_id = 6508) AND
    (rt_h_nbbo.entry_date = '2013-11-26'::date))"<br>
    "&nbsp; Buffers: shared hit=4 read=1634"<br>
    "&nbsp; I/O Timings: read=21645.468"<br>
    "Total runtime: 21655.002 ms"<br>
    <br>
    <u>Hardware</u><br>
    <br>
    Top of the line HP DL380 G7 server with 32 Gb Ram,&nbsp; P410i RAID, 10K
    SAS drives in Raid-1 config.&nbsp; Wal on separate Raid-1 volume with 15K
    SAS drives.<br>
    The only unusual thing here is that I'm running on Windows Server
    2008 R2.<br>
  </body>
</html>

--------------090504030502020704000500--