Timeline for Query optimization for multiple joins postgres
Current License: CC BY-SA 3.0
9 events
| when toggle format | what | by | license | comment | |
|---|---|---|---|---|---|
| Nov 8, 2014 at 2:04 | comment | added | Mladen Uzelac | The bad thing about Materialized view that they copy the data into new table. Be careful about that. You can always run \d+ command in psql so you can see the size of tables and views and other postgres "relations". You can also do cluster on index (phisically rearrange data). You might look into pg_repack extension. ;) The pg_repack only works on linux or it is currently supported. And then you have various tweaking on postgresql hardware setup. ;) | |
| Nov 8, 2014 at 1:55 | comment | added | Programmer | I guess when there are around 2 million rows, using order by with index to retrieve 5K records is faster. | |
| Nov 8, 2014 at 1:52 | comment | added | Programmer |
From postgres docs postgresql.org/docs/9.3/static/indexes-ordering.html Indexes are more useful when only a few rows need be fetched. An important special case is ORDER BY in combination with LIMIT n: an explicit sort will have to process all the data to identify the first n rows, but if there is an index matching the ORDER BY, the first n rows can be retrieved directly, without scanning the remainder at all.
|
|
| Nov 8, 2014 at 1:40 | comment | added | Programmer |
Thanks for answering my question. I will create an index on AccountHolderId key wherever its used as foreign key. It makes sense. The user can also load the next 5000 records via app(records 5001 to 10000,10001 to 15000 and so on). At any point of time 5K records can be loaded(different LIMIT and OFFSET values). That's why I thought of creating an index on LastModificationTimeStamp. I need to learn more about Materialized views to be able to comment on it. I will look into it. Thanks again.
|
|
| Nov 7, 2014 at 19:10 | history | edited | Mladen Uzelac | CC BY-SA 3.0 |
added 319 characters in body
|
| Nov 7, 2014 at 17:57 | history | edited | Mladen Uzelac | CC BY-SA 3.0 |
added 353 characters in body; deleted 8 characters in body
|
| Nov 7, 2014 at 17:00 | history | edited | Mladen Uzelac | CC BY-SA 3.0 |
added 244 characters in body
|
| Nov 7, 2014 at 16:43 | review | First posts | |||
| Nov 7, 2014 at 16:51 | |||||
| Nov 7, 2014 at 16:42 | history | answered | Mladen Uzelac | CC BY-SA 3.0 |