FAQ
Hi

I have a reasonable sized table (~2m rows), with an indexed field for which
there are ~200 possible values.

I want a quick way to find out if there are any rows matching a specified
value for this indexed field. I dont care how many there are or what their
properties are, just if there are any or not.

Here's a quick example:

CREATE TABLE mytable(c1 INT4 PRIMARY KEY, c2 INT2);
CREATE INDEX myindex ON mytable(c2);

SELECT COUNT(c2) AS num FROM mytable WHERE c2=1234;

The time it takes to execute the above query appears to be of the order O(n)
- is there an O(1) query to do it - given that I only want to know if num>0
or if num=0? (the function COUNT() doesn't have to be used).

Any help appreciated,

Thanks

--
Shane

Search Discussions

  • Chester c young at Mar 25, 2002 at 9:41 pm

    I want a quick way to find out if there are any rows matching a
    specified value for this indexed field. I dont care how many there
    are or what their properties are, just if there are any or not.

    SELECT COUNT(c2) AS num FROM mytable WHERE c2=1234;

    Thanks
    Shane

    Try: select 1 as yes from mytable where c2=1234 limit 1;
    or: select 1 as yes where exists( select 1 from mytable where c2=1234
    );

    would be interesting to see differences in performance on your data


    __________________________________________________
    Do You Yahoo!?
    Yahoo! Movies - coverage of the 74th Academy Awards®
    http://movies.yahoo.com/
  • Shane Wright at Mar 26, 2002 at 11:55 pm
    Hi

    Thanks, just tried this on my dev box (650,000 rows, ~50,000 per ID), the
    original way (COUNT()) takes ~1/2 second (guestimating), while your way is as
    instant as I can tel..

    Beauty :)

    --
    Shane
    On Monday 25 Mar 2002 9:41 pm, chester c young wrote:
    I want a quick way to find out if there are any rows matching a
    specified value for this indexed field. I dont care how many there
    are or what their properties are, just if there are any or not.

    SELECT COUNT(c2) AS num FROM mytable WHERE c2=1234;

    Thanks
    Shane
    Try: select 1 as yes from mytable where c2=1234 limit 1;
    or: select 1 as yes where exists( select 1 from mytable where c2=1234
    );

    would be interesting to see differences in performance on your data


    __________________________________________________
    Do You Yahoo!?
    Yahoo! Movies - coverage of the 74th Academy Awards®
    http://movies.yahoo.com/

    ---------------------------(end of broadcast)---------------------------
    TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-sql @
categoriespostgresql
postedMar 25, '02 at 8:16p
activeMar 26, '02 at 11:55p
posts3
users2
websitepostgresql.org
irc#postgresql

2 users in discussion

Shane Wright: 2 posts Chester c young: 1 post

People

Translate

site design / logo © 2021 Grokbase