Here is the query:

    "Users" AS "User"
    "User"."name" LIKE 'garr%'
AND "User"."id" NOT IN (2449214)
AND "User"."hellbanned" IS NULL
AND "User"."hellbanPostsAfterDate" IS NULL
    "followerCount" DESC NULLS LAST

Which yields:

Limit  (cost=0.43..3662.92 rows=3 width=1711) (actual time=181.935..2158.898 rows=3 loops=1)
  ->  Index Scan using users_search_followercount_index on "Users" "User"  (cost=0.43..280791.25 rows=230 width=1711) (actual time=181.933..2158.891 rows=3 loops=1)
        Filter: (((name)::text ~~ 'garr%'::text) AND (id <> 2449214))
        Rows Removed by Filter: 29434
Total runtime: 2158.951 ms

The index used is the most optimal I could build:

CREATE INDEX users_search_followercount_index ON "Users" ("followerCount" DESC NULLS LAST) WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL;

I also tried these:

-- CREATE UNIQUE INDEX users_name_unique_index ON "Users" ("name");
-- CREATE INDEX users_followerCount_index ON "Users" ("followerCount" DESC NULLS LAST);
-- CREATE UNIQUE INDEX users_search_name_followercount_unique_index ON "Users" ("name", "followerCount" DESC NULLS LAST) WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL; -- N/A
-- CREATE UNIQUE INDEX users_search_followercount_name_unique_index ON "Users" ("followerCount" DESC NULLS LAST, "name") WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL; -- 5422
-- CREATE INDEX users_name_unique_index ON "Users" ("name") WHERE "hellbanned" IS NULL AND "hellbanPostsAfterDate" IS NULL; -- 5422

But none speed it up.

  • There is something seriously wrong with your hardware, it look like. 2 seconds for an index scan on less than 30k rows sounds really bad.
    – dezso
    Feb 9 '15 at 9:03
  • Yes, it is. I'm running Amazon RDS with a T2 Medium. Everything is below 20% usage bug my Freeable Memory is ~0-3GB, which could be why this takes so long.
    – Garrett
    Feb 9 '15 at 13:33
  • A question for performance optimization needs to provide more information. Instructions in the tag info for [postgresql-performance]. Feb 10 '15 at 1:07
  • I agree with dezso, may be something seriously wrong with your hardware
    – francs
    Feb 10 '15 at 3:58

Your index attempts are still futile, since the predicate

u."name" LIKE 'garr%'

is obviously the most selective one but cannot use a basic (default) b-tree index unless you are operating with the "C" locale (which is uncommon).

FROM   "Users"
WHERE  name LIKE 'garr%'
AND    id <> 2449214
AND    hellbanned IS NULL
AND    "hellbanPostsAfterDate" IS NULL

There are several options (also depending on the missing table definition and other information), this index should work:

CREATE INDEX "Users_whatever_idx"
ON     "Users" (name text_pattern_ops, "followerCount" DESC NULLS LAST) 
WHERE  hellbanned IS NULL
AND   "hellbanPostsAfterDate" IS NULL;

The conditions on hellbanned and "hellbanPostsAfterDate" are only useful if they exclude more than a few rows. Else, a complete index might be the better choice, since it can be used for more different queries.

Also, there is a chance the second column in the index "followerCount" won't help, since there are typically multiple different matches for the first and Postgres has to sort after all. Test a couple of typical queries with and without the second column in the index.

Details for indexes and pattern matching, including an explanation for the operator class text_pattern_ops:

Aside: My standing advise is to use unquoted, legal, lower-case identifiers exclusively in Postgres.

  • Ugh, sorry, the identifier mismatch is my fault, I tried to remove all the double quotes and other noise but did only half of the work. I rolled my change back.
    – dezso
    Feb 10 '15 at 6:13
  • @dezso: Ah, I see. Adapted my answer accordingly. Feb 10 '15 at 12:57
  • 1
    Amazing, down to 0.127ms!
    – Garrett
    Feb 10 '15 at 18:39
  • @Garrett: Nice, factor 20k. :) Did you try with and / or without "followerCount" as second column in the index? Feb 10 '15 at 20:35

Maybe you will try to use nested query...

select * from (SELECT
    Users AS u
    u."name" LIKE 'garr%'
AND u.id NOT IN (2449214)
) AS u 
WHERE  u.hellbanned IS NULL
AND u.hellbanPostsAfterDate IS NULL
    followerCount DESC NULLS LAST
  • 3
    How will this help with the issue?
    – dezso
    Feb 9 '15 at 20:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.