Order of columns in a compound index in PostgreSQL (and query order)

Question

I have a table with 50K rows. It is actually a PostGIS table.

The query has 4 parts (1 mandatory) (3 Optional)

1. intersection box (a geography rectangle) with 4 lat,long (I use st_intersects) [Mandatory]
2. Date Range (min, max) on a date field
3. File type (a set of up to 8 text values) currently using IN( .....) but I can make that a temp table if needed. I see a lot of people don't like IN.
4. Country (a text value).

I expect about 100 - 4,000 returned rows

If I create a compound index on the table, which column should I use first. The fine grained is probably the location (data is spread over the world). I currently have it as GIST index.

The other indexes would be BTREE.

My intuition says use fine grained, and course last. E.g. There are only about 12 file types, so that would be very big buckets for the index.

What do the PostgreSQL and PostGIS gurus (who know the internals of the system) say?

---

UPDATE:

Let me sharpen this question.

1. I don't want anyone to have to do the work I should do. I respect your time too much. So I will get to the explain analyze later.
2. All I was looking for was some pointers and tips and guidelines.
3. I read this excellent little posting: https://devcenter.heroku.com/articles/postgresql-indexes#managing-and-maintaining-indexes about indexes
4. What I normally do is create 4 separate indexes (geo-box, country name, file_type, and date) but what want to see what a composite query would do.

Tell me if any of these assumptions are wrong. (I am pretty new to the idea of compound indexes)

1. Order is important. Choose as the first index the one that will cut the rows down the most (in my case the location (geography) which is a simple polygon or multi-polygon would do the best).
2. Sometimes queries will skip indexes. But if I create a compound query with key (#1, #2, #3, #4) then even if the user creates something that asks for #1, #3 the planner will still use the single composite query, since they order is maintained.
3. Normally I would create three BTREE queries, and one GIST (for the geography type). PostGIS does not support creating a compound out of multiple index-types. So I will have to use GIST the compound index. But that should not hurt things.
4. If I do create some additional compound or single value indexes, the planner is smart enough to pick the most intelligent one.....
5. Country Name can have about 250 different values, and is obviously strongly linked to location (geobox), but if the next best index for cutting down the row size is file_type I should use that next. I don't expect the users to often use country or date in their query sets.
6. I do NOT have to worry about creating a compound index of 4 keys will greatly increase the size of the index data. I.e. if a one-key index would be 90% of the performance boost, it does not hurt to add 3 more items to make it compound. Conversely, I should really create both indexes. A Single geography index, and also a compound index, and let the planner figure out which is best, and it will take into account the size of the index table.

Again, I am not asking anyone to design my solution, I don't mooch of the the work of others. But I do need stuff that the PostGreSQL documentation does not tell me about implementation

[The reason I don't have a EXPLAIN result to show yet, is that I have to create this 25K row table from a 24M row table. It is taking more time than I thought. I am clustering things into 1,000 item groups, and letting the user query against the 25K row table. But my next question, will involve using the results of that query to go to the MASTER 25M row table and pull things out, and that is where the performance of the compound index will really HIT].

---

sample query below:

---

SELECT
    public.product_list_meta_mv.cntry_name       AS country,
    public.product_list_meta_mv.product_producer AS producer,
    public.product_list_meta_mv.product_name     AS prod_name,
    public.product_list_meta_mv.product_type     AS ptype,
    public.product_list_meta_mv.product_size     AS size,
    ST_AsGeoJSON(public.product_list_meta_mv.the_geom, 10, 2)          AS outline
FROM
    public.product_list_meta_mv 
WHERE
    public.product_list_meta_mv.cntry_name = 'Poland' 
AND
    ST_Intersects(public.product_list_meta_mv.the_geom,
    st_geogfromtext('SRID=4326;POLYGON((21.23107910156250 51.41601562500000,
                                        18.64379882812500 51.41601562500000,
                                        18.64379882812500 48.69415283203130,
                                        21.23107910156250 48.69415283203130,
                                        21.23107910156250 51.41601562500000))')) 
AND (date >= '1/2/1900 5:00:00 AM' 
 AND date <= '2/26/2014 10:26:44 PM')
AND (public.product_list_meta_mv.product_type in
    ('CIB10','DTED0','DTED1','DTED2','CIB01','CIB05')) ;

---

EXPLAIN ANALYZE results (I did not put in any compound indexes, and from the speed I am seeing I don't know if I need to).

"Bitmap Heap Scan on catalog_full cat  (cost=4.33..37.49 rows=1 width=7428) (actual time=1.147..38.051 rows=35 loops=1)"
"  Recheck Cond: ('0103000020E61000000100000005000000000000005838354000000000AEB0494000000000A0A7324000000000AEB0494000000000A0A73240000000006C5D48400000000058383540000000006C5D4840000000005838354000000000AEB04940'::geography && outline)"
"  Filter: (((type)::text = ANY ('{CADRG,CIB10,DTED1,DTED2}'::text[])) AND (_st_distance('0103000020E61000000100000005000000000000005838354000000000AEB0494000000000A0A7324000000000AEB0494000000000A0A73240000000006C5D48400000000058383540000000006C5D4840000000005838354000000000AEB04940'::geography, outline, 0::double precision, false) < 1e-005::double precision))"
"  Rows Removed by Filter: 61"
"  ->  Bitmap Index Scan on catalog_full_outline_idx  (cost=0.00..4.33 rows=8 width=0) (actual time=0.401..0.401 rows=96 loops=1)"
"        Index Cond: ('0103000020E61000000100000005000000000000005838354000000000AEB0494000000000A0A7324000000000AEB0494000000000A0A73240000000006C5D48400000000058383540000000006C5D4840000000005838354000000000AEB04940'::geography && outline)"
"Total runtime: 38.109 ms"

---

EXPLAIN ANALYZE SELECT pid,product_name,type,country,date,size,cocom,description,egpl_date,ST_AsGeoJSON(outline, 10, 2) AS outline 
FROM portal.catalog_full AS cat 
WHERE ST_Intersects(st_geogfromtext('SRID=4326;POLYGON((21.2200927734375 51.38031005859375, 18.65478515625 51.38031005859375, 18.65478515625 48.7298583984375, 21.2200927734375 48.7298583984375, 21.2200927734375 51.38031005859375))'), cat.outline) 
AND (cat.type in ('CADRG','CIB10','DTED1','DTED2'))

Answer 1

As a part of my work I maintain a fairly large PostgreSQL database (around 120gb on disk, several multi-million-row tables) and have collected a few tricks on how to speed up the queries. First some comments on your assumptions:

1. Yes, order is important, but it's only the first one that is really different, the rest are second class indexes.
2. I'm not sure it will always use both, my guess is that the query planner will use #1, then do something clever with the rest.
3. I have no experience with GIST.
4. Yes, add all indexes first, see what is used the most and what gives the best performance.
5. I would sugest that you try both and measure what works best. Try rewriting the sql with different subqueries, maybe country and time in one, then join with the intersect-query. I have not noticed any performance problem with IN-clauses, as long as the IN-list is not thousands of elements long. My guess is that a few different queries specifically tuned depending on the input criteria available will give the best results.

6. I would suggest against making a 4-way index. Try creating one and then check the size, they can get really huge. In my experience, four 1-key indexes have been almost as fast as a single 4-way index. A trick that does work nicely for some specific queries are partial indexes, ie something like this:

   CREATE INDEX ON table_x (key1, key2, key3) WHERE some_x_column = 'XXXX';

I have created aliases in my .psqlrc-file with queries to help find what indexes to add or remove. Feel free to have a look at them over at GitHub: .psql

I use the :seq_scans and :bigtables a lot, and then \d table_name to get details about the table. Don't forget to reset the statistics after you've done some changes, select pg_stat_reset();

Answer 2

In my experience, if you want to fetch quite a large number of rows, compound index can boost your performance greatly only if you add all the fields in your SELECT and your WHERE clauses of the query, so that Postgres will run an Index-Only scan.

My queries got faster about two or three times after using a 5-field compound index that resulted in an Index-Only scan, instead of single-field indexes.

Also, from my experience, a 5-Field index uses about twice of disk space compared to a single-field index, both of which having the same first field.(Tested on a database with about 100 million rows)

Finally, Thanks to Claes for his great tips, specially about partial indexes. I'm definitely going to give it a shot.

Answer 3

I think the thing most likely to help (if anything) would be to add product_type as a 2nd column to the gist index. But without knowing how many rows match each of the AND conditions (in isolation) for your typical/problematic queries, we can only guess.

When I approach this, the first thing I do is run the query in simplified form where the WHERE clause only has one condition, each one taken in turn, under EXPLAIN ANALYZE. Look at both the estimated rows and the actual rows for each one.