Postgres Pro
Downloads
Home   >   mailing lists

Small table selection extremely slow! - Mailing list pgsql-sql

From Bo Guo
Subject Small table selection extremely slow!
Date
Msg-id CADHFRcjzJoRiPoJ_5Rf9ynetBPt0R5c4L_N_k-uja=W1+izvVg@mail.gmail.com
Whole thread Raw
Responses Re: Small table selection extremely slow!
Re: Small table selection extremely slow!
List pgsql-sql
Tree view
Hi,  

The following query took 20 seconds on a small table of 108 rows with a dozen columns:

SELECT * FROM azgiv.layers;

Here is the vacuum analyze result:

VACUUM (VERBOSE, ANALYZE) azgiv.layers

INFO:  vacuuming "azgiv.layers"
INFO:  table "layers": found 0 removable, 200 nonremovable row versions in 12 out of 12 pages
INFO:  vacuuming "pg_toast.pg_toast_52182"
INFO:  table "pg_toast_52182": index scan bypassed: 35 pages from table (0.69% of total) have 140 dead item identifiers
INFO:  table "pg_toast_52182": found 136 removable, 6 nonremovable row versions in 36 out of 5070 pages
INFO:  analyzing "azgiv.layers"
INFO:  "layers": scanned 12 of 12 pages, containing 200 live rows and 0 dead rows; 200 rows in sample, 200 estimated total rows
VACUUM

Here is what the explan shows:

EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM azgiv.layers;

Seq Scan on layers  (cost=0.00..14.00 rows=200 width=233) (actual time=0.010..0.087 rows=200 loops=1)
  Buffers: shared hit=12
Planning:
  Buffers: shared hit=51
 Planning Time: 0.233 ms
 Execution Time: 0.121 ms

I am afraid that I have missed something obvious.  Please kindly point it out.  Many thanks!

Bo

pgsql-sql by date:

Previous
From: Feike Steenbergen
Date:
Subject: Re: How to pass arguments in postgres to sql scripts.
Next
From: David Rowley
Date:
Subject: Re: Small table selection extremely slow!