Обсуждение: Postgres not willing to use an index?

I have a table, like this:

CREATE TABLE transactions
(
   transaction_id integer NOT NULL DEFAULT
nextval('transactions_seq'::regclass),
   transaction_type integer NOT NULL,
   transaction_client_id integer NOT NULL,
   transaction_destination_id integer NOT NULL,
   transaction_operator_id integer NOT NULL,
   transaction_application_id integer NOT NULL,
   transaction_application_service character varying NOT NULL,
   transaction_quantity integer NOT NULL,
   transaction_time_commit timestamp with time zone NOT NULL,
   transaction_time_received timestamp with time zone NOT NULL,
   transaction_gateway_id character(36) NOT NULL,
   transaction_payment_amount integer NOT NULL DEFAULT 0,
   CONSTRAINT transactions_pk PRIMARY KEY (transaction_id),
   CONSTRAINT transactions_uq__gateway_id UNIQUE (transaction_gateway_id)
)
WITH (OIDS=FALSE);

Now, all the _type, client_id, destination_id, operator_id, and
application_id are foreigen-keyed to coresponding tables. There are no
indices on those columns.

Besides PK and uq-constraint indices I have this index:

CREATE INDEX transactions_idx__client_data ON transactions
USING btree (transaction_client_id, transaction_destination_id,
transaction_operator_id, transaction_application_id,
transaction_time_commit)

The table_count is like this:

jura=# select count(*) from transactions;
   count
----------
  13751457
(1 row)

There are roughly 500.000 - 600.000 transactions for each month. There
are also transactions from past two years in the table.

I often SELECT data from the table for specified time period - usualy
from begining to the end of the month, like this:

SELECT <some-columns> FROM transactions WHERE transaction_time_commit
BETWEEN '2009-01-01' AND '2009-01-31 23:59:59';

The problem is that postgres is never using an index:

jura=# explain analyze select * from transactions where
transaction_time_commit between '2009-01-01' and '2009-01-31 23:59:59';

             QUERY PLAN


---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Seq Scan on transactions  (cost=0.00..416865.85 rows=593713 width=91)
(actual time=4.067..3918.629 rows=525051 loops=1)
    Filter: ((transaction_time_commit >= '2009-01-01
00:00:00+01'::timestamp with time zone) AND (transaction_time_commit <=
'2009-01-31 23:59:59+01'::timestamp with time zone))
  Total runtime: 4026.404 ms
(3 rows)

Time: 4068.521 ms


If I force it not to use sequential scans, it is using index, with
benefits of shorter execution time:
jura=# set enable_seqscan to false;
SET
Time: 0.103 ms
jura=# explain analyze select * from transactions where
transaction_time_commit between '2009-01-01' and '2009-01-31 23:59:59';

                  QUERY PLAN


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on transactions  (cost=410369.98..629869.67
rows=593713 width=91) (actual time=1060.569..1280.500 rows=525051 loops=1)
    Recheck Cond: ((transaction_time_commit >= '2009-01-01
00:00:00+01'::timestamp with time zone) AND (transaction_time_commit <=
'2009-01-31 23:59:59+01'::timestamp with time zone))
    ->  Bitmap Index Scan on transactions_idx__client_data
(cost=0.00..410221.55 rows=593713 width=0) (actual
time=1058.992..1058.992 rows=525051 loops=1)
          Index Cond: ((transaction_time_commit >= '2009-01-01
00:00:00+01'::timestamp with time zone) AND (transaction_time_commit <=
'2009-01-31 23:59:59+01'::timestamp with time zone))
  Total runtime: 1388.882 ms
(5 rows)

Time: 1396.737 ms


Now, I found interesting is that if I create index just on
transaction_time_commit column (and I leave
transactions_idx__client_data index), then postgres is using that new index.

Also, if I change idx__client_data index like this (first I drop it, and
then I create new one):

CREATE INDEX transactions_idx__client_data ON transactions
USING btree (transaction_client_id, transaction_destination_id,
transaction_time_commit);

then postgres is using that index:

jura=# explain analyze select * from transactions where
transaction_time_commit between '2009-01-01' and '2009-01-31 23:59:59';

                  QUERY PLAN


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on transactions  (cost=349473.37..568973.06
rows=593713 width=91) (actual time=949.224..1128.848 rows=525051 loops=1)
    Recheck Cond: ((transaction_time_commit >= '2009-01-01
00:00:00+01'::timestamp with time zone) AND (transaction_time_commit <=
'2009-01-31 23:59:59+01'::timestamp with time zone))
    ->  Bitmap Index Scan on transactions_idx__client_data
(cost=0.00..349324.94 rows=593713 width=0) (actual time=947.678..947.678
rows=525051 loops=1)
          Index Cond: ((transaction_time_commit >= '2009-01-01
00:00:00+01'::timestamp with time zone) AND (transaction_time_commit <=
'2009-01-31 23:59:59+01'::timestamp with time zone))
  Total runtime: 1234.989 ms
(5 rows)

Time: 1235.727 ms



Now, I have many 'selects' on the transactions table (still, not as many
as inserts), mostly filtered on transaction_time, client_id,
destination_id and application_id, but there is fair amount of 'selects'
filtered only on transaction_time.

Now, shall I keep the original index and add another one on just
transaction_time (there is, I guess, overhead of maintaining two
indices), or shall I remove transaction_time from original index, and
create another one?

And, is it normal for postgres to 'ignore' the transaction_time column
in original index?


This is the postgres version I'm using:
jura=# select version();
                                                     version

----------------------------------------------------------------------------------------------------------------
  PostgreSQL 8.3.5 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 4.1.2
20061115 (prerelease) (Debian 4.1.1-21)
(1 row)


    Mike

On Fri, Feb 6, 2009 at 3:43 PM, Mario Splivalo
<mario.splivalo@megafon.hr> wrote:

> Besides PK and uq-constraint indices I have this index:
>
> CREATE INDEX transactions_idx__client_data ON transactions
> USING btree (transaction_client_id, transaction_destination_id,
> transaction_operator_id, transaction_application_id,
> transaction_time_commit)

I think it is because it isn't just a simple index, and for some
reason planner decides - that going through every
transaction_application_id, etc, etc just to find right
transaction_time_commit isn't worth the hassle.
Try using few more indexes, on less columns.
Also, if I may - I don't think it is quite usefull to have column
names that include table name in every single one of them, makes
things so much less readable.

--
GJ

Mario Splivalo <mario.splivalo@megafon.hr> writes:
> Besides PK and uq-constraint indices I have this index:

> CREATE INDEX transactions_idx__client_data ON transactions
> USING btree (transaction_client_id, transaction_destination_id,
> transaction_operator_id, transaction_application_id,
> transaction_time_commit)

> SELECT <some-columns> FROM transactions WHERE transaction_time_commit
> BETWEEN '2009-01-01' AND '2009-01-31 23:59:59';
> The problem is that postgres is never using an index:

Hardly surprising --- a search on the index's lowest-order column would
require scanning practically all of the index.  (If you think about the
ordering of the index entries you'll see why.)  If this is a typical
query then you need a separate index on transaction_time_commit.

The fine manual goes into some detail about how to design indexes;
http://www.postgresql.org/docs/8.3/static/indexes.html
particularly 11.3, 11.5.

            regards, tom lane

On Fri, Feb 6, 2009 at 11:14 AM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Mario Splivalo <mario.splivalo@megafon.hr> writes:
>> Besides PK and uq-constraint indices I have this index:
>
>> CREATE INDEX transactions_idx__client_data ON transactions
>> USING btree (transaction_client_id, transaction_destination_id,
>> transaction_operator_id, transaction_application_id,
>> transaction_time_commit)
>
>> SELECT <some-columns> FROM transactions WHERE transaction_time_commit
>> BETWEEN '2009-01-01' AND '2009-01-31 23:59:59';
>> The problem is that postgres is never using an index:
>
> Hardly surprising --- a search on the index's lowest-order column would
> require scanning practically all of the index.  (If you think about the
> ordering of the index entries you'll see why.)  If this is a typical
> query then you need a separate index on transaction_time_commit.
>
> The fine manual goes into some detail about how to design indexes;
> http://www.postgresql.org/docs/8.3/static/indexes.html
> particularly 11.3, 11.5.

What's weird about this example is that when he sets enable_seqscan to
off, the bitmap index scan plan is actually substantially faster, even
though it in fact does scan nearly the entire heap.  I don't
understand how it can be faster to scan the index and the heap than to
just scan the heap.

...Robert

>>> Robert Haas <robertmhaas@gmail.com> wrote:
> What's weird about this example is that when he sets enable_seqscan
to
> off, the bitmap index scan plan is actually substantially faster,
even
> though it in fact does scan nearly the entire heap.  I don't
> understand how it can be faster to scan the index and the heap than
to
> just scan the heap.

It's cached in the second test, maybe?

-Kevin

On Fri, Feb 6, 2009 at 12:41 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:
>>>> Robert Haas <robertmhaas@gmail.com> wrote:
>> What's weird about this example is that when he sets enable_seqscan to
>> off, the bitmap index scan plan is actually substantially faster, even
>> though it in fact does scan nearly the entire heap.  I don't
>> understand how it can be faster to scan the index and the heap than to
>> just scan the heap.
>
> It's cached in the second test, maybe?

I gather that the results were repeatable, but perhaps Mario could
double-check that?

...Robert

Tom Lane wrote:
>
> Hardly surprising --- a search on the index's lowest-order column would
> require scanning practically all of the index.  (If you think about the
> ordering of the index entries you'll see why.)  If this is a typical
> query then you need a separate index on transaction_time_commit.

Yes, actually I just moved transaction_time_commit column to the
begining of the index, since, most of the time I run queries based on
transaction_time_commit and then transaction_client_id and
transaction_destination_id.

> The fine manual goes into some detail about how to design indexes;
> http://www.postgresql.org/docs/8.3/static/indexes.html
> particularly 11.3, 11.5.

I see it now. I read the manual concerning CREATE INDEX command, and
there is no mention of multicolumn indices, did not notice Note that
points to Chapter 11.

    Mike

Robert Haas wrote:
> On Fri, Feb 6, 2009 at 12:41 PM, Kevin Grittner
> <Kevin.Grittner@wicourts.gov> wrote:
>>>>> Robert Haas <robertmhaas@gmail.com> wrote:
>>> What's weird about this example is that when he sets enable_seqscan to
>>> off, the bitmap index scan plan is actually substantially faster, even
>>> though it in fact does scan nearly the entire heap.  I don't
>>> understand how it can be faster to scan the index and the heap than to
>>> just scan the heap.
>> It's cached in the second test, maybe?
>
> I gather that the results were repeatable, but perhaps Mario could
> double-check that?

I think that it is always cached - the machine has 4GB of RAM, and i'm
just using it for testing. Now, I think that the cache is used because
there is no I/O wait when I run the queries (if you have any suggestion
on how to check cache usage, since I have no idea):

jura=# set enable_seqscan to true;
SET
jura=# explain analyze select * from transactions where
transaction_time_commit between '2008-01-01' and '2008-01-31 23:59:59';

             QUERY PLAN


---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Seq Scan on transactions  (cost=0.00..418365.68 rows=759775 width=91)
(actual time=928.342..3788.232 rows=722176 loops=1)
    Filter: ((transaction_time_commit >= '2008-01-01
00:00:00+01'::timestamp with time zone) AND (transaction_time_commit <=
'2008-01-31 23:59:59+01'::timestamp with time zone))
  Total runtime: 3936.744 ms
(3 rows)

jura=# set enable_seqscan to false;
SET
jura=# explain analyze select * from transactions where
transaction_time_commit between '2008-01-01' and '2008-01-31 23:59:59';

                  QUERY PLAN


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on transactions  (cost=428882.89..651630.52
rows=759775 width=91) (actual time=1358.040..1633.867 rows=722176 loops=1)
    Recheck Cond: ((transaction_time_commit >= '2008-01-01
00:00:00+01'::timestamp with time zone) AND (transaction_time_commit <=
'2008-01-31 23:59:59+01'::timestamp with time zone))
    ->  Bitmap Index Scan on transactions_idx__client_data
(cost=0.00..428692.95 rows=759775 width=0) (actual
time=1354.485..1354.485 rows=722176 loops=1)
          Index Cond: ((transaction_time_commit >= '2008-01-01
00:00:00+01'::timestamp with time zone) AND (transaction_time_commit <=
'2008-01-31 23:59:59+01'::timestamp with time zone))
  Total runtime: 1778.938 ms
(5 rows)


Now, transactions_idx__client_data index has transaction_time_commit as
the last column in index.

When I 'recreate' the database, and run the queries again, first run
which uses sequential scan is around 10 seconds, heavy I/O, any
subsequent query run takes cca 3900 msecs, as shown above.

When I say 'disable seqscan', planner uses Bitmap Index Scan, as shown
above, just that the first query takes around 25 seconds to run, with
heavy I/O. Any subsequent query runs take somewhat less than 2 seconds,
as shown above.

I'm not sure on what to do to minimize the impact of the OS-cache, apart
from taking RAM modules out of the machine - if you have any suggestions
I'll try to apply them.

On production database I changed the index so that
'transaction_time_commit' is the first column, and now I don't get any
sequential scans on transactions table when only WHERE condition is on
transaction_time_commit.

    Mike

Mario Splivalo wrote:
> Robert Haas wrote:
> jura=# set enable_seqscan to false;
> SET
> jura=# explain analyze select * from transactions where
> transaction_time_commit between '2008-01-01' and '2008-01-31 23:59:59';
>
>                  QUERY PLAN
>
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

>
>  Bitmap Heap Scan on transactions  (cost=428882.89..651630.52
> rows=759775 width=91) (actual time=1358.040..1633.867 rows=722176 loops=1)
>    Recheck Cond: ((transaction_time_commit >= '2008-01-01
> 00:00:00+01'::timestamp with time zone) AND (transaction_time_commit <=
> '2008-01-31 23:59:59+01'::timestamp with time zone))
>    ->  Bitmap Index Scan on transactions_idx__client_data
> (cost=0.00..428692.95 rows=759775 width=0) (actual
> time=1354.485..1354.485 rows=722176 loops=1)
>          Index Cond: ((transaction_time_commit >= '2008-01-01
> 00:00:00+01'::timestamp with time zone) AND (transaction_time_commit <=
> '2008-01-31 23:59:59+01'::timestamp with time zone))
>  Total runtime: 1778.938 ms
> (5 rows)
>
>

I neglected to paste this 'explain analyze', when I changed the index so
that 'transaction_time_commit' is first column in the index:

jura=# explain analyze select * from transactions where
transaction_time_commit between '2009-01-01' and '2009-01-31 23:59:59';

                  QUERY PLAN


-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  Bitmap Heap Scan on transactions  (cost=7550.51..233419.58 rows=250880
width=91) (actual time=95.139..280.008 rows=525051 loops=1)
    Recheck Cond: ((transaction_time_commit >= '2009-01-01
00:00:00+01'::timestamp with time zone) AND (transaction_time_commit <=
'2009-01-31 23:59:59+01'::timestamp with time zone))
    ->  Bitmap Index Scan on transactions_idx__client_data
(cost=0.00..7487.79 rows=250880 width=0) (actual time=93.382..93.382
rows=525051 loops=1)
          Index Cond: ((transaction_time_commit >= '2009-01-01
00:00:00+01'::timestamp with time zone) AND (transaction_time_commit <=
'2009-01-31 23:59:59+01'::timestamp with time zone))
  Total runtime: 386.665 ms
(5 rows)

Thank you, Tom!

    Mike