Re: 7.3.1 New install, large queries are slow - Mailing list pgsql-performance

Tom and Tomasz:
I have to change the datatype of batchdetail.batchid from int4 to int8.  After over 4 hours, the UPDATE transfer from
theold column to new has not yet completed.  After that I still have to build a new index and run VACUUM FULL.  When
thatis 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
ideabased 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
horribletime 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
mixingint4 with int8 in a lot of them.  All of these tables (except batchdetail) were migrated using pgAdminII's
migrationwizard, so I didn't really give a hard look at all the data types matching up since it has a nice data map (I
usedthe 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
typesare 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
conceivablygrow beyond 2 billion someday (although I'd be archiving a lot of it when it got that big).  Maybe I just
shouldn'tworry 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
anyPK 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
theseduplicate indexes I created (and then a VACUUM FULL).  Perhaps an extra sentence in the docs might prevent someone
elsefrom 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.