Skip site navigation (1) Skip section navigation (2)

Pros / cons for indexing a small table

From: Rikard Bosnjakovic <rikard(dot)bosnjakovic(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: Pros / cons for indexing a small table
Date: 2009-11-10 11:25:41
Message-ID: d9e88eaf0911100325v687bba40g937945571f8fc313@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
I'm having a simple table for managing hit counts on WWW. The table
was setup some year ago using this:

CREATE TABLE hit_counts (
  page varchar(255),
  hits integer,
  last_visit timestamp,
  id serial primary key
);

When updating a hit, I use this PHP-code:

  $q = "SELECT hits FROM hit_counters WHERE page='$this_uri'";
  $res = pg_query($db, $q);
  if (pg_num_rows($res) == 0) {
    $q = "INSERT INTO hit_counters (page) VALUES ('$this_uri')";
  } else {
    $row = pg_fetch_object($res);
    $hits = $row->hits;
    $hits++;
    $q = "UPDATE hit_counters SET last_visit = (SELECT
localtimestamp(0)), hits = $hits WHERE page='$this_uri'";
  }
  pg_query($db, $q);

In other words, if the actual page is not in the table it is inserted.
Otherwise the "hits" is increased by one. The id-column is never used,
I simply appended it because of "good practise" using a primary key
for all my tables.

Now, my questions are:

1. Since the id-column is never used, I will probably drop it to save
some space. Or, is there any point in keeping it?

2. From what I've read in the manual and what other people have told
me, creating an index can speed up table accessing. I tried by adding
an index to the page-column in the above table. Then by using ANALYZE
and EXPLAIN, the cost got reduced from 259 to 6 which I guess is a
pretty big save although I'm not sure what the value really is. Is
creating an index on 'page' the best way to speed up this particular
table?

3. The page-column is queried using full length strings (no regexps or
wildcards). Will there be any performance issues if I have the
page-column indexed and then run wildcard-queries on it? What I mean
is, if I run wildcard-queries on this indexed column, will the index
be of any use at all?


-- 
- Rikard

Responses

pgsql-novice by date

Next:From: Josh KupershmidtDate: 2009-11-10 19:41:08
Subject: Re: Pros / cons for indexing a small table
Previous:From: Jasen BettsDate: 2009-11-10 10:16:16
Subject: Re: Table design for basic user management

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group