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

Re: selects from large tables

From: Stephan Szabo <sszabo(at)megazone23(dot)bigpanda(dot)com>
To: Nikk Anderson <Nikk(dot)Anderson(at)parallel(dot)ltd(dot)uk>
Cc: <pgsql-performance(at)postgresql(dot)org>
Subject: Re: selects from large tables
Date: 2002-11-18 13:02:03
Message-ID: 20021118045236.A49278-100000@megazone23.bigpanda.com (view raw or flat)
Thread:
Lists: pgsql-performance
On Mon, 18 Nov 2002, Nikk Anderson wrote:

> Any ideas on how we can select data more quickly from large tables?

Are these row estimates realistic? It's estimating nearly 20 million rows
to be returned by some of the queries (unless I'm misreading the
number - possible since it's 5am here).  At that point you almost
certainly want to be using a cursor rather than plain queries since even a
small width result (say 50 bytes) gives a very large (1 gig) result set.

> - Queries and explain plans
>
> select count(*) from table_name;
> NOTICE:  QUERY PLAN:
> Aggregate  (cost=488700.65..488700.65 rows=1 width=0)
>   ->  Seq Scan on table_name  (cost=0.00..439527.12 rows=19669412 width=0)
>
> hawkdb=# explain select count(job_id) from table_name;
> NOTICE:  QUERY PLAN:
> Aggregate  (cost=488700.65..488700.65 rows=1 width=4)
>   ->  Seq Scan on table_name  (cost=0.00..439527.12 rows=19669412 width=4)
>
> hawkdb=# explain select * from table_name;
> NOTICE:  QUERY PLAN:
> Seq Scan on table_name  (cost=0.00..439527.12 rows=19669412 width=57)
>
> hawkdb=# explain select count(*) from table_name where job_id = 13;
> NOTICE:  QUERY PLAN:
> Aggregate  (cost=537874.18..537874.18 rows=1 width=0)
>   ->  Seq Scan on table_name  (cost=0.00..488700.65 rows=19669412 width=0)
>
> hawkdb=# explain select * from table_name where job_id = 13;
> NOTICE:  QUERY PLAN:
> Seq Scan on http_result  (cost=0.00..488700.65 rows=19669412 width=57)
>
> hawkdb=# explain select * from table_name where job_id = 1;
> NOTICE:  QUERY PLAN:
> Index Scan using http_result_pk on table_name  (cost=0.00..5.01 rows=1
> width=57)
>
> hawkdb=#explain select * from table_name where time > '2002-10-10';
> NOTICE:  QUERY PLAN:
> Seq Scan on table_name  (cost=0.00..488700.65 rows=19649743 width=57)
>
> hawkdb=# explain select * from http_result where time < '2002-10-10';
> NOTICE:  QUERY PLAN:
> Index Scan using table_name_time on table_name  (cost=0.00..75879.17
> rows=19669 width=57)


In response to

Responses

pgsql-performance by date

Next:From: FedericoDate: 2002-11-18 15:02:17
Subject: Re: for/loop performance in plpgsql ?
Previous:From: Nikk AndersonDate: 2002-11-18 12:32:45
Subject: selects from large tables

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