Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>When you say <i>The field_instances table is indexed on both form_instance_id and field_id</i> you mean that there are separate indexes on form_instance_id and field_id on that table?</p> <p>Try dropping the index on <code>form_instance_id</code> and put a concatenated index on <code>(form_instance_id, field_id)</code>.</p> <p>An index works by giving you a quick lookup that tells you where the rows are that match your index. It then has to fetch through those rows to do what you want. So you always want your index to be as specific as possible. If you put two indexes on the table, you'll have two different ways to do a lookup, but a query will usually only take advantage of one of them. If you put a <em>concatenated</em> index on the table, you'll be able to look up on the first field in the index, the first two fields, etc efficiently. (So a concatenated index on <code>(a, b)</code> gives you fast lookups on <code>a</code>, even faster lookups on both <code>a</code> and <code>b</code>, but doesn't help you look things up on <code>b</code>)</p> <p>Right now it is figuring out all possible things in <code>form_instances</code> that have the right state. It separately figures out all of the <code>field_instances</code> that have the right field id. It then does a hash join. For this makes a lookup hash from one result set, and scans the other for matches.</p> <p>With my suggestion it should figure out all possible <code>form_instances</code> of interest. It will then go to the index, and figure out all of the <code>field_instances</code> that match on <strong>both</strong> the form instance and field id, and then it will find exactly the results of interest. Because the index is more specific, the database will have fewer rows of data to deal with to process your query.</p>
    singulars
    1. This table or related slice is empty.
    1. POHow can I optimize this postgresql query?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USbtilly
    1. USbtilly
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1 - this would seem like the best solution to the problem with the information provided - fingers crossed!
      singulars
      1. PO
      1. USWill A
    2. COAs you suspected, I had two separate indexes, one for form_instance_id and one for field_id on the field_instances table. However, I followed your advice, and dropped the form_instance_id index and created the new index on (form_instance_id, field_id), but it doesn't make a difference. The new plan doesn't even seem to use that index. Instead it uses a different index I had created for a different query on (answer,field_id).
      singulars
      1. PO
      1. USzippy
    3. CO@zippy: OK, now the PostgreSQL optimizer is just doing stupid things. I'd need to have access to the system to play with it for a while to figure out why it is being so silly, and figure out how to trick it into doing the right thing. Sorry I can't be of more help.
      singulars
      1. PO
      1. USbtilly
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload