Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'))
share|improve this question

migrated from stackoverflow.com Feb 28 at 17:09

This question came from our site for professional and enthusiast programmers.

1  
Provide the actual query, please. –  ypercube Feb 28 at 16:29
    
Does the "3 optional" mean that the query can have 8 different variations (depending on whether the 2,3,4 options are activated or not)? –  ypercube Feb 28 at 16:32
    
There are 4 AND components to the WHERE. On the st_intersects is required, the others might be there, or they might not. But I want to deal with the case where they are all present. –  Dr.YSG Feb 28 at 16:59
2  
I voted to migrate the question to dba.se, this is a complex query with multiple range conditions. –  ypercube Feb 28 at 17:03
1  
Show EXPLAIN ANALYZE for the query. –  Craig Ringer Feb 28 at 22:44

2 Answers 2

up vote 3 down vote accepted

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();

share|improve this answer
    
These are excellent tips. I took your advice, and then used this to do an experiment on a much larger table that we maintain (43 million rows). The results are at: dba.stackexchange.com/questions/61084/… –  Dr.YSG Mar 17 at 15:01

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.

share|improve this answer
    
see my update above, but I think you are giving me a good lead, think about ordering the indexes by which cuts the row output down fastest. Is that right? –  Dr.YSG Mar 3 at 18:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.