Re: Slow query: bitmap scan troubles

От:
Тема: Re: Slow query: bitmap scan troubles
Дата: ,
Msg-id: 093301cdd233$057a2b30$106e8190$@foo.me.uk
(см: обсуждение, исходный текст)
Ответ на: Slow query: bitmap scan troubles  (<>)
Список: pgsql-performance

Скрыть дерево обсуждения

Slow query: bitmap scan troubles  (<>, )
 Re: Slow query: bitmap scan troubles  (<>, )
 Re: Slow query: bitmap scan troubles  (Claudio Freire, )
  Re: Slow query: bitmap scan troubles  (Jeff Janes, )
   Re: Slow query: bitmap scan troubles  (Claudio Freire, )
    Re: Slow query: bitmap scan troubles  (<>, )
     Re: Slow query: bitmap scan troubles  (Vitalii Tymchyshyn, )
      Re: Slow query: bitmap scan troubles  (<>, )
      Re: Slow query: bitmap scan troubles  ("Philip Scott", )
     Re: Slow query: bitmap scan troubles  (Sergey Konoplev, )
      Re: Slow query: bitmap scan troubles  (<>, )
     Re: Slow query: bitmap scan troubles  (Jeff Janes, )
    Re: Slow query: bitmap scan troubles  ("Philip Scott", )
   Re: Slow query: bitmap scan troubles  (<>, )
    Re: Slow query: bitmap scan troubles  (Claudio Freire, )
    Re: Slow query: bitmap scan troubles  (Jeff Janes, )
     Re: Slow query: bitmap scan troubles  (Jeff Janes, )
      Re: Slow query: bitmap scan troubles  (Claudio Freire, )
       Re: Slow query: bitmap scan troubles  (Jeff Janes, )
        Re: Slow query: bitmap scan troubles  (Claudio Freire, )
         Re: Slow query: bitmap scan troubles  (Jeff Janes, )
      Re: Slow query: bitmap scan troubles  (Tom Lane, )
       Re: Slow query: bitmap scan troubles  (<>, )
     Re: Slow query: bitmap scan troubles  (<>, )
     Re: Slow query: bitmap scan troubles  (Guillaume Lelarge, )
      Re: Slow query: bitmap scan troubles  (<>, )
  Re: Slow query: bitmap scan troubles  ("Philip Scott", )
 Re: Slow query: bitmap scan troubles  (Claudio Freire, )
  Re: Slow query: bitmap scan troubles  (<>, )
 Re: Slow query: bitmap scan troubles  ("Kevin Grittner", )

Bad form to reply to yourself I know but just check-reading that for the
third time I noticed two mistakes

- The box has 128Gb of ram, not 512Mb

- There is an additional constraint on the position_effect table (though I
don't think it matters for this discussion):
     CONSTRAINT cons_pe_trade FOREIGN KEY (id_trade) REFERENCES trade (id)

Sorry to clog your inboxes further!

Regards,

Philip

-----Original Message-----
From: 
[mailto:] On Behalf Of

Sent: 04 December 2012 15:07
To: 
Subject: [PERFORM] Slow query: bitmap scan troubles

Hi guys (and girls)

I've been banging my head over this for a few days now so if any of you kind
souls could take a minute to take a look at this I would be eternally
grateful.

I have a pretty straightforward query that is very slow by default, and
about 70 times faster when I set enable_bitmapscan=off. I would like to
convince the planner to use my lovely indexes.

The scenario is this; I have two tables, trade and position_effect. A trade
is a deal we do with somebody to exchange something for something else. It
has a time it was done, and is associated with a particular book for
accounting purposes. A position effect records changes to our position (e.g.
how much we have) of an particular asset. One trade can many position
effects (usually only 1,2 or 3)

For example, I do a trade of USD/GBP and I get two position effects, +1000
GBP and -1200USD


SCHEMA:
-------

The actual schema is a bit more complicated but I will put the important
parts here (if you think it important, the full schema for the two tables is
here: http://pastebin.com/6Y52aDFL):

CREATE TABLE trade
(
  id bigserial NOT NULL,
  time_executed timestamp with time zone NOT NULL,
  id_book integer NOT NULL,
  CONSTRAINT cons_trade_primary_key PRIMARY KEY (id),
)

CREATE INDEX idx_trade_id_book
  ON trade
  USING btree
  (id_book, time_executed, id);

CREATE TABLE position_effect
(
  id bigserial NOT NULL,
  id_trade bigint NOT NULL,
  id_asset integer NOT NULL,
  quantity double precision NOT NULL,
  CONSTRAINT cons_pe_primary_key PRIMARY KEY (id_trade, id_asset),
)

SETUP:
------

These tables are relatively large (~100 million rows in position effect).
The box is a pretty beastly affair with 512Mb of ram and 4x10 2Ghz cores.
The postgres configuration is here:

http://pastebin.com/48uyiak7

I am using a 64bit postgresql 9.2.1, hand compiled on a RedHat 6.2 box.

QUERY:
------

What I want to do is sum all of the position effects, for a particular asset
while joined to the trade table to filter for the time it was executed and
the book it was traded into:

SELECT sum(position_effect.quantity)
      FROM trade, position_effect
      WHERE trade.id = position_effect.id_trade
         AND position_effect.id_asset = 1837
         AND trade.time_executed >= '2012-10-28 00:00:00'
         AND trade.id_book = 41

In this case there are only 11 rows that need to be summed. If I just let
postgres do its thing, that query takes 5000ms (Which when multiplied over
many books and assets gets very slow). I think this is because it is
bitmapping the whole position_effect table which is very large. If I disable
bitmap scans:

set enable_bitmapscan = off;

The query takes 43ms, and properly uses the indexes I have set up.

Slow version with bitmapscan enabled: http://explain.depesz.com/s/6I7 Fast
version with bitmapscan disabled: http://explain.depesz.com/s/4MWG




--
Sent via pgsql-performance mailing list ()
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance





В списке pgsql-performance по дате сообщения:

От: Sergey Konoplev
Дата:
Сообщение: Re: Slow query: bitmap scan troubles
От: Jeff Janes
Дата:
Сообщение: Re: Slow query: bitmap scan troubles