pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect query takes long to execute
6+ messages / 6 participants
[nested] [flat]
* Select query takes long to execute
@ 2003-05-29 13:58 Kevin Schroeder <mirage@mirageworks.com>
0 siblings, 2 replies; 6+ messages in thread
From: Kevin Schroeder @ 2003-05-29 13:58 UTC (permalink / raw)
To: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Select query takes long to execute
@ 2003-05-29 14:24 Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
parent: Kevin Schroeder <mirage@mirageworks.com>
1 sibling, 0 replies; 6+ messages in thread
From: Shridhar Daithankar @ 2003-05-29 14:24 UTC (permalink / raw)
To: pgsql-performance
On 29 May 2003 at 8:58, Kevin Schroeder wrote:
> 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.
Check yor shared buffers setting and effective OS cache setting. If these are
appropriately tuned, then it should be fast enough.
Is the table vacuumed? Is index taking too much space? Then try reindexing. It
might help as vacuum does not reclaim wasted space in index.
HTH
Bye
Shridhar
--
Wait! You have not been prepared! -- Mr. Atoz, "Tomorrow is Yesterday",
stardate 3113.2
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Select query takes long to execute
@ 2003-05-29 16:01 scott.marlowe <scott.marlowe@ihs.com>
parent: Kevin Schroeder <mirage@mirageworks.com>
1 sibling, 0 replies; 6+ messages in thread
From: scott.marlowe @ 2003-05-29 16:01 UTC (permalink / raw)
To: Kevin Schroeder <mirage@mirageworks.com>; +Cc: pgsql-performance
See if lowering random_page_cost to 1.5 or so helps here.
That and effective_cache_size are two of the more important values the
planner uses to decide between seq scans and index scans.
On Thu, 29 May 2003, 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
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Select query takes long to execute
@ 2003-05-30 02:44 Nikolaus Dilger <nikolaus@dilger.cc>
0 siblings, 1 reply; 6+ messages in thread
From: Nikolaus Dilger @ 2003-05-30 02:44 UTC (permalink / raw)
To: mirage@mirageworks.com; +Cc: 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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Select query takes long to execute
@ 2003-05-30 08:57 SZUCS Gábor <surrano@mailbox.hu>
parent: Nikolaus Dilger <nikolaus@dilger.cc>
0 siblings, 1 reply; 6+ messages in thread
From: SZUCS Gábor @ 2003-05-30 08:57 UTC (permalink / raw)
To: pgsql-performance
Nikolaus,
I think that shouldn't be any more effective. As I experienced, it's
irrelevant how many cols an index has as long as you only use the first
column. And, after that, if you use another column, how could a missing
second column be any better?
G.
------------------------------- cut here -------------------------------
----- Original Message -----
From: "Nikolaus Dilger" <nikolaus@dilger.cc>
Sent: Friday, May 30, 2003 4:44 AM
> Kevin,
>
> How about creating a new index just on column6?
> That should be much more effective than the multicolumn
> index.
>
> Regards,
> Nikolaus
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Select query takes long to execute
@ 2003-05-30 12:13 Bruno Wolff III <bruno@wolff.to>
parent: SZUCS Gábor <surrano@mailbox.hu>
0 siblings, 0 replies; 6+ messages in thread
From: Bruno Wolff III @ 2003-05-30 12:13 UTC (permalink / raw)
To: SZUCS Gábor <surrano@mailbox.hu>; +Cc: pgsql-performance
On Fri, May 30, 2003 at 10:57:33 +0200,
SZUCS Gábor <surrano@mailbox.hu> wrote:
> Nikolaus,
>
> I think that shouldn't be any more effective. As I experienced, it's
> irrelevant how many cols an index has as long as you only use the first
> column. And, after that, if you use another column, how could a missing
> second column be any better?
Because the index will be more compact and reside on less disk blocks.
The planner also makes different guesses for the selectivity whne using
the first column of a multicolumn index as opposed to a single column
index.
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2003-05-30 12:13 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-05-29 13:58 Select query takes long to execute Kevin Schroeder <mirage@mirageworks.com>
2003-05-29 14:24 ` Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
2003-05-29 16:01 ` scott.marlowe <scott.marlowe@ihs.com>
2003-05-30 02:44 Re: Select query takes long to execute Nikolaus Dilger <nikolaus@dilger.cc>
2003-05-30 08:57 ` SZUCS Gábor <surrano@mailbox.hu>
2003-05-30 12:13 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox