Postgres Pro
Downloads
Home   >   mailing lists

Thread: index problem (uses one index but not the other)

index problem (uses one index but not the other)

From
Maurice Balick
Date:
Hello,

I have a table with about 600000 records and two indexes:
  CREATE TABLE transactions (
    type INT2,
    order_id INT4,
    account_id INT4,
    market_id INT4,
    qty INT4,
    price INT2,
    timestamp INT8,
    memo varchar(30));

  CREATE INDEX trans_aid_idx ON transactions USING HASH (account_id);
  CREATE INDEX trans_oid_idx ON transactions USING HASH (order_id);

When I make a query on an account_id it uses the trans_aid_idx index,
but when I make a query on an order_id it does a sequential scan instead
of using the trans_oid_idx index:

  nf=# explain select * from transactions where account_id = 12345;
  NOTICE:  QUERY PLAN:

  Index Scan using trans_aid_idx on transactions  (cost=0.00..496.99
rows=139 width=40)

  EXPLAIN
  nf=# explain select * from transactions where order_id = 12345;
  NOTICE:  QUERY PLAN:

  Seq Scan on transactions  (cost=0.00..11490.45 rows=5061 width=40)

  EXPLAIN

I have vacuum analyzed the entire DB and just the transactions table, I
have
dropped and recreated the table and the indexes. But it does seem to help.

The weird thing is that this used to work (i.e. trans_oid_idx was used)
when there was about 200000 records (about 1/3 of now). Also, there is
about
9000 distinct values of account_id, but about 300000 values of order_id.

Is there a problem when the number of distinct values grows too large?

(I am running Postgresql 7.1.3 on Redhat 7.1)

Thanks for any help/advice.

--Maurice

Re: index problem (uses one index but not the other)

From
Tom Lane
Date:
Maurice Balick <balm@smiley.com> writes:
> The weird thing is that this used to work (i.e. trans_oid_idx was used)
> when there was about 200000 records (about 1/3 of now). Also, there is
> about
> 9000 distinct values of account_id, but about 300000 values of order_id.

What's the most common value in each case?

> (I am running Postgresql 7.1.3 on Redhat 7.1)

7.2 is less likely to be fooled when the most common value is much more
common than the rest ...

            regards, tom lane

Re: index problem (uses one index but not the other)

From
Maurice Balick
Date:
Yes, there are 42000+ records with order_id = 0, no other order_id is present in more than 10 records.<br /><br /> Is
thereany simple way to solve this problem without going to 7.2? (I'd rather go to 7.3 directly in a month or two).<br
/><br/> --Maurice<br /><br /> PS: Thanks for always being there for 'us', the plain users. <br /><br /><br /> Tom Lane
wrote:<br/><blockquote cite="mid:9810.1018936158@sss.pgh.pa.us" type="cite"><pre wrap="">Maurice Balick <a
class="moz-txt-link-rfc2396E"href="mailto:balm@smiley.com"><balm@smiley.com></a> writes:<br /></pre><blockquote
type="cite"><prewrap="">The weird thing is that this used to work (i.e. trans_oid_idx was used)<br />when there was
about200000 records (about 1/3 of now). Also, there is <br />about<br />9000 distinct values of account_id, but about
300000values of order_id.<br /></pre></blockquote><pre wrap=""><br />What's the most common value in each case?<br
/><br/></pre><blockquote type="cite"><pre wrap="">(I am running Postgresql 7.1.3 on Redhat 7.1)<br
/></pre></blockquote><prewrap=""><br />7.2 is less likely to be fooled when the most common value is much more<br
/>commonthan the rest ...<br /><br />            regards, tom lane<br /><br />---------------------------(end of
broadcast)---------------------------<br/>TIP 4: Don't 'kill -9' the postmaster<br /><br />.<br /><br
/></pre></blockquote><br/>

Re: index problem (uses one index but not the other)

From
Maurice Balick
Date:
I'm sorry, I meant to say 7.2.1 not 7.3 and now I see that it's available.
So I guess I'll go try it and see if it solves my index problem.

--Maurice

Maurice Balick wrote:
   Yes, there
are 42000+ records with order_id = 0, no other order_id is present in more
than 10 records.

 Is there any simple way to solve this problem without going to 7.2? (I'd
rather go to 7.3 directly in a month or two).

 --Maurice

 PS: Thanks for always being there for 'us', the plain users.


 Tom Lane wrote:

    Maurice Balick <balm@smiley.com> writes:

      The weird thing is that this used to work (i.e. trans_oid_idx was used)when there was about 200000 records (about
1/3of now). Also, there is about9000 distinct values of account_id, but about 300000 values of order_id. 

      What's the most common value in each case?

        (I am running Postgresql 7.1.3 on Redhat 7.1)

        7.2 is less likely to be fooled when the most common value is much morecommon than the rest ...
regards,tom lane---------------------------(end of broadcast)---------------------------TIP 4: Don't 'kill -9' the
postmaster.