SEO

Hello everyone!!

I have a table with 17 columns and it has almost
530000 records and doing just a

SELECT * FROM table

with the EXPLAIN ANALYZE I get:

Seq Scan on table (cost=0.00...19452.95 rows=529395
width=170) (actual time=0.155...2194.294 rows=529395
loops=1)
total runtime=3679.039 ms

and this table has a PK...
Do you think is too much time for a simple select?...

I guess it's a bit slow to get all those records...but
since I'm a newbie with PostgreSQL, what I can check
to optimize?

Thanks to all!
Ciao,
Luigi

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

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

On Wed, Apr 9, 2008 at 3:21 PM, Luigi N. Puleio <npuleio@rocketmail.com> wrote:
> Hello everyone!!
>
> I have a table with 17 columns and it has almost
> 530000 records and doing just a
>
> SELECT * FROM table
>
> with the EXPLAIN ANALYZE I get:
>
> Seq Scan on table (cost=0.00...19452.95 rows=529395
> width=170) (actual time=0.155...2194.294 rows=529395
> loops=1)
> total runtime=3679.039 ms
>
> and this table has a PK...
> Do you think is too much time for a simple select?...
>

Well, PK won't help you here because you are selecting all rows
from the table and that seq scan is the right thing for that.
Without knowing your hardware its difficult to judge if
the time taken is more or not. Anyways, I don't think there is much
tweaking you can do for such a query except making sure that
your table is not bloated with dead tuples.

Thanks,
Pavan


--
Pavan Deolasee
EnterpriseDB http://www.enterprisedb.com

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

Pavan Deolasee wrote:

> Anyways, I don't think there is much
> tweaking you can do for such a query except making sure that
> your table is not bloated with dead tuples.

To the OP:

More explicitly: Make sure you use autovacuum or run VACUUM manually on
the table periodically.

Would I be correct in suspecting that your real problem is with a more
meaningful and complex query, and the one you've posted is
oversimplifying what you are trying to do? If that is the case, and
you're having problems with queries that do more real work than this one
does, maybe you should post EXPLAIN ANALYZE output from such a real
world query.

--
Craig Ringer

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

On Wed, 9 Apr 2008, Pavan Deolasee wrote:
>> I have a table with 17 columns and it has almost
>> 530000 records and doing just a
>>
>> SELECT * FROM table

> Well, PK won't help you here because you are selecting all rows
> from the table and that seq scan is the right thing for that.

Yes. Like he said. Basically, you're asking the database to fetch all half
a million rows. That's going to take some time, whatever hardware you
have. The PK is completely irrelevant, because the query doesn't refer to
it at all. To be honest, three seconds sounds pretty reasonable for that
sort of query.

Matthew

--
There once was a limerick .sig
that really was not very big
It was going quite fine
Till it reached the fourth line

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

--- Pavan Deolasee <pavan.deolasee@gmail.com> wrote:

> On Wed, Apr 9, 2008 at 3:21 PM, Luigi N. Puleio
> <npuleio@rocketmail.com> wrote:
> > Hello everyone!!
> >
> > I have a table with 17 columns and it has almost
> > 530000 records and doing just a
> >
> > SELECT * FROM table
> >
> > with the EXPLAIN ANALYZE I get:
> >
> > Seq Scan on table (cost=0.00...19452.95
> rows=529395
> > width=170) (actual time=0.155...2194.294
> rows=529395
> > loops=1)
> > total runtime=3679.039 ms
> >
> > and this table has a PK...
> > Do you think is too much time for a simple
> select?...
> >
>
> Well, PK won't help you here because you are
> selecting all rows
> from the table and that seq scan is the right thing
> for that.
> Without knowing your hardware its difficult to judge
> if
> the time taken is more or not. Anyways, I don't
> think there is much
> tweaking you can do for such a query except making
> sure that
> your table is not bloated with dead tuples.
>

In effect, this simple query is a start of examination
to check about speed for another nested query; more
precisely I'm tring to obtain the difference of the
time querying the same table with a different
condition, like:

SELECT
(a.column1)::date, MIN(b.column2) - a.column2
FROM
table a
inner join table b
on ((a.column1)::date = (b.column1)::date amd
b.column3 = 'b' and (b.column1)::time without time
zone >= (a.column1)::time without time zone)
WHERE
(a.column1)::date = '2008-04-09'
a.column3 = 'a'
GROUP BY a.column1

and with this I have to obtain like 3-4 records from
all those whole 500000 records and with the explain
analyze I get almost 6 seconds:

Nested Loop (cost=0.00...52140.83 rows=1 width=34)
(actual time=4311.756...5951.271 rows=1 loops=1)

So its been a lot of time because I could wonder how
long it would take for example if I do a filter not
for a single day but for a month which should return
much more than 1 row...

Actually I emailed to the responsible of the server
where PostgreSQL is installed to see if he done a
vacuum manually lately since querying the pg_settings
or the pg_stat_all_tables I have no response about
autovacuum...

But maybe there's a better way to query this nested
loop for more efficience....

Thanks to all!
Ciao,
Luigi

__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

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

Luigi N. Puleio wrote:

> SELECT
> (a.column1)::date, MIN(b.column2) - a.column2
> FROM
> table a
> inner join table b
> on ((a.column1)::date = (b.column1)::date amd
> b.column3 = 'b' and (b.column1)::time without time
> zone >= (a.column1)::time without time zone)
> WHERE
> (a.column1)::date = '2008-04-09'
> a.column3 = 'a'
> GROUP BY a.column1
>
> and with this I have to obtain like 3-4 records from
> all those whole 500000 records and with the explain
> analyze I get almost 6 seconds:
>
> Nested Loop (cost=0.00...52140.83 rows=1 width=34)
> (actual time=4311.756...5951.271 rows=1 loops=1)

With all that casting, is it possible that appropriate indexes aren't
being used because your WHERE / ON clauses aren't an exact type match
for the index?

Can you post the full EXPLAIN ANALYZE from the query? This snippet
doesn't even show how records are being looked up.

What about a \d of the table from psql, or at least a summary of the
involved column data types and associated indexes?

--
Craig Ringer

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