Why fetch a row is more slow than a 'selec * from'

Started by eroblesover 14 years ago2 messagesgeneral
Jump to latest
#1erobles
erobles@sensacd.com.mx

Hello
I've been looking for ways to optimize a query.

I have a table with 120,000 records. When searched on:

select * from big_table

takes to run: 3 min.

I wanted to use cursors and the query with big_table, it taked 11
minutes. It caught my attention on a small_table (100 records)
because the time, with cursors, were reduced by half.

The EXPLAIN ANALYZE: indicates that a search is sequential, but has a
primary key

Will have some advice to optimize the response time of the visit? there
is nothing to do? :(
El contenido de este correo electrónico y sus archivos adjuntos son privados y confidenciales y va dirigido exclusivamente a su destinatario. No se autoriza la utilización, retransmisión, diseminación, o cualquier otro uso de esta información por un receptor o entidades distintas al destinatario. Si recibe este correo sin ser el destinatario se le solicita eliminarlo y hacerlo del conocimiento del emisor. La empresa no se hace responsable de transmisiones o comunicaciones no autorizadas o emitidas por personas ajenas a sus colaboradores utilizando éste medio electrónico.

The content of this email and its attached files are private and confidential and intended exclusively for the use of the individual or entity to which they are addressed. The retransmission, dissemination, or any other use of this information other than by the intended recipient is prohibited. If you have received this email in error please delete it and notify the sender. The company cannot be held liable for unauthorized electronic transmissions or communications, nor for those emitted by non-company individuals and entities.

#2Pavel Stehule
pavel.stehule@gmail.com
In reply to: erobles (#1)
Re: Why fetch a row is more slow than a 'selec * from'

Hello

cursors are optimized for fast return of first row and there are no
expect to return complete result.

This can be a problem

try to set cursor_tuple_fraction to 1

Regards

Pavel Stehule

2011/11/7 Ing.Edmundo.Robles.Lopez <erobles@sensacd.com.mx>:

Show quoted text

Hello
I've been looking for ways to optimize a query.

I have a table with 120,000 records. When searched on:

select * from big_table

takes to run: 3 min.

I wanted to use cursors and the query with big_table,  it taked 11 minutes.
It caught my attention  on a  small_table (100 records) because  the time,
with cursors, were reduced by half.

The EXPLAIN ANALYZE: indicates that a search is sequential, but has a
primary key

Will have some advice to optimize the response time of the visit? there is
nothing to do? :(
El contenido de este correo electrónico y sus archivos adjuntos son privados
y confidenciales y va dirigido exclusivamente a su destinatario.  No se
autoriza la utilización, retransmisión, diseminación, o cualquier otro uso
de esta información por un receptor o entidades distintas al destinatario.
 Si recibe este correo sin ser el destinatario se le solicita eliminarlo y
hacerlo del conocimiento del emisor. La empresa no se hace responsable de
transmisiones o comunicaciones no autorizadas o emitidas por personas ajenas
a sus colaboradores utilizando éste medio electrónico.

The content of this email and its attached files are private and
confidential and intended exclusively for the use of the individual or
entity to which they are addressed. The retransmission, dissemination, or
any other use of this information other than by the intended recipient is
prohibited.  If you have received this email in error please delete it and
notify the sender.  The company cannot be held liable for unauthorized
electronic transmissions or communications, nor for those emitted by
non-company individuals and entities.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general