Search Postgresql Archives

Optimizing tables for known queries?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello!  This is my first time on this list, so forgive me if it is not he correct place to discuss things like this.  I have a question and answer tables right now that have grown large enough that my querying of them has become extremely slow and was wondering if anyone had tips on ways of optimizing for known queries.


Here is a simplified view of what I currently have:


QuestionsTable
id, text
---------------------------
1,  What is your Gender?
2,  What is your Age?
3,  Rank these foods


QuestionsOptionsTable
id, qid,  text,  type
---------------------------
1,  1,    Male,   row
2,  1,    Female, row
3,  2,    18,     row
4,  2,    19,     row
5,  2,    20,     row
6,  2,    25,     row
7,  2,    30,     row
8,  3,    Great,  column
9,  3,    Good,   column
10, 3,    Bad,    column
11, 3,    Chinese,row
12, 3,    Mexican,row
13, 3,    Italian,row



Respondents
id, name
---------------------------
1, John
2, Susie
3, Bob


ResponsesTable
rid, qid, oid1, oid2
---------------------------
1,   1,   1,     NULL       # John, Male
1,   2,   6,     NULL       # John, 25
1,   3,   11,     8         # John, Chinese is Great
1,   3,   12,     9         # John, Mexican is Good
1,   3,   13,     9         # John, Italian is Good
2,   1,   2,     NULL       # Susie, Female
2,   2,   7,     NULL       # Susie, 30
2,   3,   11,     10         # Susie, Chinese is Bad
2,   3,   12,     9          # Susie, Mexican is Great
3,   2,    6,    NULL       # Bob, 25
3,   3,    11,    8,          # Bob, Chinese is Great
3,   3,    12,    10         # Bob, Mexican is Bad


The types of queries I need to execute on a regular basis are rolling up question counts and comparing them, but the actual query is ad-hoc, so I can't easily statically update counts every time a new response comes in.

An example query would be:
Query: "Show me a comparison of Question 3 between Male and Female between the ages of 20-30"
Answer:
Males,     25, Chinese, Great,  2  <-- Total count (Bob and John both answered this)
Females, 30, Chinese, Bad,     1
Females, 30, Mexican, Great,  1

etc... (text instead of ids shown here to ease understanding)


What I'm wondering is if there is a more denormalized view of this type of data that would make those of types of queries quicker?

Thanks,
John
   

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux