Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2009
    Posts
    2

    Unanswered: Slow Select Query Based on Inexperience

    Hi guys,

    I recently decided to take the plunge into PostgreSQL from Microsoft Access/Jet. My database has a table of a little over two million records.

    My problem is that when I run the following query on Access
    "Select * from data", all the data appears in a second or less. When I run that same query (Select * from data) in pgAdminIII, it takes over 90 seconds to complete.

    I assume I am just doing something incorrectly. If anyone can give me advice or point me in the right direction for a google search, it would be appreciated!

    This is my table definition:
    att=# \d+ data

    Table "public.data"
    Column | Type | Modifiers | Description
    -----------+------------------+-----------+-------------
    subservid | smallint | not null |
    vertid | smallint | not null |
    stateid | smallint | not null |
    sizeid | smallint | not null |
    myear | smallint | not null |
    spend | double precision | not null |
    Indexes:
    "data_pkey" PRIMARY KEY, btree (subservid, vertid, stateid, sizeid, myear)
    "size_idx" btree (sizeid)
    "state_idx" btree (stateid)
    "subserv_idx" btree (subservid)
    "vert_idx" btree (vertid)
    "year_idx" btree (myear)
    Has OIDs: no

    I have also analyzed the query and got these results:

    EXPLAIN ANALYZE select * from data;

    "Seq Scan on data (cost=0.00..40283.88 rows=2460888 width=18) (actual time=0.018..4658.533 rows=2460888 loops=1)"
    "Total runtime: 9158.541 ms"

  2. #2
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    In access the first pageful of rows are returned asynchronously & displayed, and the gui keeps on loading the records in the background. In pgiii, they apparently are using a synchronous approach, where nothing is displayed until all data is loaded.

    With this type of query, the indexes aren't helping, as, with no where clause, pg knows it has to scan through all the records to return everything to you. Try adding a where clause to your test query to see how it helps (and how it compares to Access then.)

    You should also think about tuning the database, if you regularly plan on dumping 36 megabyte chunks of data.

    Is postgresql installed as a local server (on the desktop machine?)
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


  3. #3
    Join Date
    Apr 2009
    Posts
    2
    Hi loquin,

    Thank you for the response. You are correct, if I add some where clauses, or do some sort of aggregation of the data it works much faster.

    The postgre database is on a local machine.

    Unfortunately I do have to dump the 2million records fairly often. Do you have any recommendations as to how I could tune the database for that?

    Thanks!

    - brad

  4. #4
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    one of the posts in the postgresql faq (first post in the pg forum) has a post with some links pertaining with pg tuning.

    also, the manual is pretty good, too.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •