Re: selects from large tables

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)