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

Re: Select query takes long to execute

From: "Nikolaus Dilger" <nikolaus(at)dilger(dot)cc>
To: mirage(at)mirageworks(dot)com
Cc: pgsql-performance(at)postgresql(dot)org
Subject: Re: Select query takes long to execute
Date: 2003-05-30 02:44:50
Message-ID: 20030529194451.24416.h006.c001.wm@mail.dilger.cc.criticalpath.net (view raw or flat)
Thread:
Lists: pgsql-performance
Kevin,

How about creating a new index just on column6?
That should be much more effective than the multicolumn
index.

Regards,
Nikolaus

On Thu, 29 May 2003 08:58:07 -0500, "Kevin Schroeder"
wrote:

> 
> Hello,
>     I'm running a simple query on a table and I'm
> getting a very long
> response time.  The table has 56,000 rows in it.  It
> has a full text field,
> but it is not being referenced in this query.  The
> query I'm running is
> 
> select row_key, column1, column2, column3, column4,
> column5 from table1
> where column6 = 1 order by column3 desc limit 21;
> 
> There is an index on the table
> 
> message_index btree (column6, column3, column7)
> 
> Column 3 is a date type, column 6 is an integer and
> column 7 is unused in
> this query.
> 
> The total query time is 6 seconds, but I can bring
that
> down to 4.5 if I
> append "offset 0" to the end of the query.  By
checking
> query using "explain
> analyze" it shows that it is using the index.
> 
> If anyone has any ideas as to why the query is taking
> so long and what I can
> do to make it more efficient I would love to know.
> 
> Thanks
> Kevin
> 
> 
> ---------------------------(end of
> broadcast)---------------------------
> TIP 6: Have you searched our list archives?
> 
> http://archives.postgresql.org

Responses

pgsql-performance by date

Next:From: Tomas SzepeDate: 2003-05-30 07:24:42
Subject: Re: db growing out of proportion
Previous:From: Stephan SzaboDate: 2003-05-29 17:37:38
Subject: Re: db growing out of proportion

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