pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Roman Fail <rfail@posportal.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Josh Berkus <josh@agliodbs.com>
Cc: sszabo@megazone23.bigpanda.com
Cc: pgsql-performance@postgresql.org
Subject: Re: 7.3.1 New install, large queries are slow
Date: Thu, 16 Jan 2003 21:54:39 -0800
Message-ID: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C09@pos_pdc.posportal.com> (raw)

Tom and Tomasz:
I have to change the datatype of batchdetail.batchid from int4 to int8.  After over 4 hours, the UPDATE transfer from the old column to new has not yet completed.  After that I still have to build a new index and run VACUUM FULL.  When that is all done I'll re-run the various queries, including a specific small one that Josh requested.  
 
Chad Thompson suggested that I add single quotes around the literals in the WHERE clause, which sounded like a great idea based on his experience.  Unfortunately, it did not make the query any faster.  But read on!
 
For kicks, I tried this simple query, which should happen in an instant.  It is the first row in the table.
EXPLAIN ANALYZE select batchdetailid from batchdetail where batchdetailid = 27321;
 Seq Scan on batchdetail  (cost=0.00..1960485.43 rows=1 width=8) (actual time=17.58..264303.76 rows=1 loops=1)
   Filter: (batchdetailid = 27321)
 Total runtime: 264303.87 msec
Does it make sense to do a sequence scan when the primary key index is available?  Even so, it's still a pretty horrible time given the hardware.
 
HOWEVER.....look at this:
EXPLAIN ANALYZE select batchdetailid from batchdetail where batchdetailid = 27321::bigint;
 Index Scan using batchdetail_pkey on batchdetail  (cost=0.00..4.13 rows=1 width=8) (actual time=0.03..0.03 rows=1 loops=1)
   Index Cond: (batchdetailid = 27321::bigint)
 Total runtime: 0.07 msec
 
It sort of feels like a magic moment.  I went back and looked through a lot of the JOIN columns and found that I was mixing int4 with int8 in a lot of them.  All of these tables (except batchdetail) were migrated using pgAdminII's migration wizard, so I didn't really give a hard look at all the data types matching up since it has a nice data map (I used the defaults except for the money type).  
 
Now I think I'm just going to drop the entire database and reload the data from scratch, making sure that the data types are mapped exactly right.  Correct me if I'm wrong, but int4 only ranges from negative 2 billion to positive 2 billion.  All the primary keys for my tables would fit in this range with the exception of batchdetail, which could conceivably grow beyond 2 billion someday (although I'd be archiving a lot of it when it got that big).  Maybe I just shouldn't worry about it for now and make everything int4 for simplicity.
 
I doubt I will accomplish all this on Friday, but I'll give a full report once I get it all reloaded. 
 
> Stephan Szabo wrote:
> Also you have two indexes on batchdetailid right now (primary key
> also creates one) which added to the confusion.

The 7.3.1 docs for CREATE TABLE don't mention anything about automatic index creation for a PRIMARY KEY.  I didn't see any PK indexes via pgAdminII, so I read this line from the docs and decided to create them separately.
    "Technically, PRIMARY KEY is merely a combination of UNIQUE and NOT NULL"
However, this query proves you are right:
trans=# select relname, relpages, indisunique, indisprimary from pg_class, pg_index
trans-# where indexrelid in (37126739, 8604257) and pg_class.oid = pg_index.indexrelid;
 relname                          | relpages | indisunique | indisprimary
----------------------------------+----------+-------------+--------------
 batchdetail_pkey                 |   121850 | t           | t
 batchdetail_ix_batchdetailid_idx |    63934 | f           | f
 
All other columns in the two tables are identical for these two indexes.  So now I've gone through and deleted all of these duplicate indexes I created (and then a VACUUM FULL).  Perhaps an extra sentence in the docs might prevent someone else from making the same mistake as I?
 
*** Current postgresql.conf settings:
tcpip_socket=true
shared_buffers = 131072
max_fsm_relations = 10000
max_fsm_pages = 2000000
sort_mem = 32768
default_statistics_target = 30

Thanks again for all your help!
 
Roman Fail
Sr. Web Application Developer
POS Portal, Inc.
 


view thread (66+ messages)  latest in thread

Message-ID: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C09@pos_pdc.posportal.com>
Permalink:  ../9B1C77393DED0D4B9DAA1AA1742942DA0E4C09@pos_pdc.posportal.com/
Also on:    postgresql.org/message-id/9B1C77393DED0D4B9DAA1AA1742942DA0E4C09@pos_pdc.posportal.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: rfail@posportal.com, tgl@sss.pgh.pa.us, josh@agliodbs.com, sszabo@megazone23.bigpanda.com
  Subject: Re: 7.3.1 New install, large queries are slow
  In-Reply-To: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C09@pos_pdc.posportal.com>
  References: <9B1C77393DED0D4B9DAA1AA1742942DA0E4C09@pos_pdc.posportal.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox