Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a PostgreSQL table. select * is very slow whereas select id is nice and quick. I think it may be that the size of the row is very large and it's taking a while to transport, or it may be some other factor.

I need all of the fields (or nearly all of them), so selecting just a subset isn't a quick fix. Selecting the fields that I want is still slow.

Here's my table schema minus the names:

integer                  | not null default nextval('core_page_id_seq'::regclass)
character varying(255)   | not null
character varying(64)    | not null
text                     | default '{}'::text
character varying(255)   | 
integer                  | not null default 0
text                     | default '{}'::text
text                     | 
timestamp with time zone | 
integer                  | 
timestamp with time zone | 
integer                  | 

The size of the text field may be any size. But still, no more than a few kilobytes in the worst case.

Questions

  1. Is there anything about this that screams 'crazy inefficient'?
  2. Is there a way to measure page size at the Postgres command-line to help me debug this?
share|improve this question
1  
I bet that the difference comes from id being indexed while the whole row being not. If you do an EXPLAIN ANALYZE and paste its result here (or, if it is too long, explain.depesz.com ), we could see something. –  dezso Sep 7 '12 at 9:46
    
Actually... one of the columns is 11 MB. That will explain it I think. So is there a way to do length(*) rather than just length(field)? I know that's chars not bytes but I only need an approx value. –  Joe Sep 7 '12 at 9:51
1  
Here you can find two (or three) different approximations - they will give you an average. postgresql.1045698.n5.nabble.com/… –  dezso Sep 7 '12 at 10:14
1  
Are you thinking of something like: select length(row(n.*)::text) from pg_namespace n;? –  Jack Douglas Sep 7 '12 at 11:25
1  
Or simpler: select length(n::text) from pg_namespace n; –  Erwin Brandstetter Sep 7 '12 at 14:05
show 1 more comment

3 Answers

There are a few things that could be happening. In general I doubt that length is the proximal problem. I suspect instead you have a length-related problem.

You say the text fields can get up to a few k. A row cannot go over 8k in main storage, and it is likely that your larger text fields have been TOASTed, or moved out of main storage into an extended storage in separate files. This makes your main storage faster (so select id actually is faster because fewer disk pages to access) but select * becomes slower because there is more random I/O.

If your total row sizes are still all well uner 8k you could try altering the storage settings. I would however warn that you can get bad things happen when inserting an oversized attribute into main storage so best not to touch this if you don't have to and if you do, set appropriate limits via check constraints. So transportation is not likely the only thing. It may be collating many, many fields that require random reads. Large numbers of random reads may also cause cache misses, and large amounts of memory required can require that things get materialized on disk and large numbers of wide rows, if a join is present (and there is one if TOAST is involved) may require costlier join patterns, etc.

The first thing I would look at doing is selecting fewer rows and see if that helps. If that works, you could try adding more RAM to the server too, but I would start and see where the performance starts falling off due to plan changes and cache misses first.

share|improve this answer
    
Very interesting! It was 15 MB (user attaching a large file, now fixed). That explains a lot, thanks. –  Joe Sep 7 '12 at 13:14
add comment

An approximation of the size of a row, including the TOAST'ed contents, is easy to get by querying the length of the TEXT representation of the entire row:

SELECT octet_length(t.*::text) FROM tablename AS t WHERE primary_key=:value;

This is a close approximation to the number of bytes that will be retrieved client-side when executing:

SELECT * FROM tablename WHERE primary_key=:value;

...assuming that the caller of the query is requesting results in text format, which is what most programs do (binary format is possible, but it's not worth the trouble in most cases).

The same technique could be applied to locate the N "biggest-in-text" rows of tablename:

SELECT primary_key, octet_length(t.*::text) FROM tablename AS t
   ORDER BY 2 DESC LIMIT :N;
share|improve this answer
1  
Or just octet_length(t::text) (credit: Erwin) –  Jack Douglas Sep 7 '12 at 14:34
add comment

PostgreSQL provides a number of Database Object Size Functions, you can use. I packed the most interesting ones in this query and added some Statistics Access Functions.

This is going to demonstrate that the various methods to measure the "size of a row" can lead to very different results. It all depends what you want to measure exactly.

Replace schema.tbl with your table name to get a compact view of collected statistics about the size of your rows.

WITH x AS (
   SELECT count(*)                AS ct
         ,sum(length(t::text))    AS txt_len  -- length in characters
         ,'schema.tbl'::regclass AS tbl
   FROM   schema.tbl t
   )
   , y AS (
   SELECT ARRAY [
             pg_relation_size(tbl)
            ,pg_relation_size(tbl, 'vm')
            ,pg_relation_size(tbl, 'fsm')
            ,pg_table_size(tbl)
            ,pg_indexes_size(tbl)
            ,pg_total_relation_size(tbl)
            ,txt_len
          ] AS val
         ,ARRAY [
             'core_relation_size'
            ,'visibility_map'
            ,'free_space_map'
            ,'table_size_incl_toast'
            ,'indexes_size'
            ,'total_size_incl_toast_and_indexes'
            ,'live_rows_in_text_representation'
          ] AS name
   FROM   x
   )
SELECT unnest(name)                AS what
      ,unnest(val)                 AS bytes
      ,pg_size_pretty(unnest(val)) AS bytes_pretty
      ,unnest(val) / ct            AS per_row_bytes
FROM   x,y
UNION  ALL
SELECT '----------'::text, NULL::int8, '----'::text, NULL::int8
UNION  ALL
SELECT 'row_count'::text, ct
      ,NULL::text, NULL::bigint FROM x
UNION  ALL
SELECT 'live_tuples'::text, pg_stat_get_live_tuples(tbl)
      ,NULL::text, NULL::bigint FROM x
UNION  ALL
SELECT 'dead_tuples'::text, pg_stat_get_dead_tuples(tbl)
      ,NULL::text, NULL::bigint FROM x;

I only pack the values in arrays and unnest() again, so I don't have to spell out calculations for every single row repeatedly.

General row count statistics are appended at the end with unconventional SQL-foo to get everything in one query. You could wrap it into a plpgsql function for repeated use, hand in the table name as parameter and use EXECUTE.

Result:

               what                |  bytes   | bytes_pretty | per_row_bytes 
-----------------------------------+----------+--------------+---------------
 core_relation_size                | 44138496 | 42 MB        |            91 
 visibility_map                    |        0 | 0 bytes      |             0 
 free_space_map                    |    32768 | 32 kB        |             0 
 table_size_incl_toast             | 44179456 | 42 MB        |            91 
 indexes_size                      | 33128448 | 32 MB        |            68 
 total_size_incl_toast_and_indexes | 77307904 | 74 MB        |           159 
 live_rows_in_text_representation  | 29987360 | 29 MB        |            62 
 ----------                        |          | ----         |               
 row_count                         |   483424 |              |               
 live_tuples                       |   483424 |              |               
 dead_tuples                       |     2677 |              |               

The additional module pgstattuple provides more useful functions.

share|improve this answer
    
This is pure gold. –  rhyek Feb 19 at 10:23
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.