2

Check out the following simple query:

SELECT * FROM "teammgr_team" WHERE ("teammgr_team"."real" = true AND "teammgr_team"."name" = 'abc');

But it takes too long:

-------------------------------------------------------------------------------------------------------------------
 Seq Scan on teammgr_team  (cost=0.00..114772.49 rows=118 width=121) (actual time=24.544..618.185 rows=12 loops=1)
   Filter: ("real" AND ((name)::text = 'abc'::text))
   Rows Removed by Filter: 4752431
 Planning time: 0.066 ms
 Execution time: 618.217 ms
(5 rows)

I assume because the table is very large:

  count
---------
 4752443
(1 row)

This is the table and relevant column:

                                       Table "public.teammgr_team"
        Column        |         Type          |                         Modifiers
----------------------+-----------------------+-----------------------------------------------------------
 id                   | integer               | not null default nextval('teammgr_team_id_seq'::regclass)
 name                 | character varying(40) | not null

Indexes:
    "teammgr_team_pkey" PRIMARY KEY, btree (id)
    "teammgr_team_club_id" btree (club_id)

I am not sure if adding an index to a character column is advisable. I would think so but I don't know enough about databases.

So I am thinking about adding a simple index:

CREATE INDEX teammgr_team_name ON teammgr_team (name);

Just having in mind it shouldn't be UNIQUE because the team name is not unique.

  • Would adding this index help improve execution time?

  • I've gone through the docs but is there any option that would be benefitial to my goal?

1 Answer 1

2

Your query filters millions of rows to return a hand full of values. So, yes, adding this index will help massively:

CREATE INDEX teammgr_team_name_idx ON teammgr_team (name);

If your query always asks for teammgr_team."real" = true and that case is not the common case in the table, a partial index would be even better:

CREATE INDEX teammgr_team_name_real_idx ON teammgr_team (name)
WHERE real;

Or maybe a multicolumn index:

CREATE INDEX teammgr_team_name_real_idx ON teammgr_team (name, real);

But adding a boolean column as index expression has typically limited benefit. It's typically more effective to have a partial index on the rare case.

That all depends on exact data distribution. And, possibly, on typical write patterns: highly volatile columns (updated a lot) are more expensive to index.

Aside 1

Simplify:

SELECT * FROM teammgr_team WHERE real AND name = 'abc';

WHERE teammgr_team."real" = true is just a noisy way of saying WHERE real.

Aside 2

Don't use basic type names like real as identifier. Leads to confusion. And "name" is not a good name, either.

2
  • When you say "If your query always asks for teammgr_team."real" = true and that case is not the common case, a partial index would be even better" don't you mean "that case IS the common case"? In fact, most of my team queries are against real teams, so it would be a good idea for the index to target only the subset of real teams.
    – dabadaba
    Jun 21 at 23:13
  • @dabadaba: I clarified: "... and that case is not the common case in the table". If most of your rows have real = true anyway, a partial index isn't worth it. The few violating rows are filtered cheaply. Jun 22 at 0:11

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.