Re: [PERFORM] PSA: upgrade your extensions

Hi Merlin: Any EXPLAIN on the query affected? size of indexes before and after reindex? Regards, Daniel. > El 1 feb 2017, a las 13:38, Merlin Moncure escribió: > > I was just troubleshooting a strange performance issue with pg_trgm > (greatest extension over) that ran

Re: [PERFORM] Querying with multicolumn index

Hi As a quick fix: Have you considered dropping ix_updates_time index? I’ve been able to reproduce the issue, but with bigger numbers than you. When I dropped ix_updates_time it went much much faster. It uses updates_driver_id_time_idx index instead. For some reason the planner does not make

Re: [PERFORM] Querying with multicolumn index

Hi all, Thomas is absolutely right, the distribution I synthetically made, had 6M records but very old, 9M old, as you can see it had to skip 9M records before finding a suitable record using time index. EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM updates WHERE driver_id = 100 ORDER BY "time"

Re: [PERFORM] Querying with multicolumn index

t, you might find them useful. Don’t forget to show us index sizes and index configuration, please. test_bad_index_choice_bloated_index.sql Description: Binary data test_bad_index_choice_bloated_index.out Description: Binary data El 11 dic 2016, a las 7:04, Daniel Blanch Bataller <daniel.bla

Re: [PERFORM] Substantial different index use between 9.5 and 9.6

ANALYZE takes samples at random, so statistics might be different even with same postgresql version: https://www.postgresql.org/docs/current/static/sql-analyze.html For large tables, ANALYZE takes a random sample of the table contents, rather than examining every row. This allows even very

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

Hi, If just recreating the index now it uses it, it might mean that the index was bloated, that is, it grew so big that it was cheaper a seq scan. I’ve seen another case recently where postgres 9.6 wasn’t using the right index in a query, I was able to reproduce the issue crafting index

Re: [PERFORM] Slow query after 9.3 to 9.6 migration

The biggest impact on performance you can achieve is by using a materialized view. if it’s so heavily used as you said, even 2-3 seconds in a multiuser OLTP environment still unacceptable under my point of view. I don’t know if this is the case but if you have 1000 users connecting at 8 am all

Re: [PERFORM] Insert Concurrency

Yes, postgres has partitions: https://www.postgresql.org/docs/9.6/static/ddl-partitioning.html But this is not going to help much in the scenario you have. Postgres can ingest data very very fast, 100M records in seconds -

Re: [PERFORM] performance problem on big tables

See if the copy command is actually working, copy should be very fast from your local disk. > El 16 ago 2017, a las 14:26, Mariel Cherkassky > escribió: > > > After all the changes of the memory parameters the same operation(without the > copy utility) didnt

Re: [PERFORM] performance problem on big tables

7-08-16 16:08 GMT+03:00 Daniel Blanch Bataller > <daniel.blanch.batal...@gmail.com <mailto:daniel.blanch.batal...@gmail.com>>: > See if the copy command is actually working, copy should be very fast from > your local disk. > > >> El 16 ago 20

Re: [PERFORM] performance problem on big tables

Hi. In general using COPY is *much faster* than anything else. You can even split the data load and run it in parallel, start with as many jobs as processors you have. Same with indexes, run them in parallel. With parallel I mean various psql running at the same time. Tuning postgres will

Re: [PERFORM] performance problem on big tables

ble to unlogged - it improved the > performance slightly. Is there a way to make sure that I/O is the problem ? > > 2017-08-17 0:46 GMT+03:00 Daniel Blanch Bataller > <daniel.blanch.batal...@gmail.com <mailto:daniel.blanch.batal...@gmail.com>>: > Seems your disks are too s

Re: [PERFORM] performance problem on big tables

herkas...@gmail.com> > escribió: > > My server is virtual and it have virtual hd from a vnx storage machine. The > logs and the data are on the same disk. > > 2017-08-16 17:04 GMT+03:00 Daniel Blanch Bataller > <daniel.blanch.batal...@gmail.com <mailto:daniel.blanch

Re: [PERFORM] performance problem on big tables

nd postgres. Regards, Daniel. > > 2017-08-21 11:37 GMT+03:00 Daniel Blanch Bataller > <daniel.blanch.batal...@gmail.com <mailto:daniel.blanch.batal...@gmail.com>>: > >> El 21 ago 2017, a las 10:00, Mariel Cherkassky <mariel.cherkas...@gmail.com >> &

Re: [PERFORM] performance problem on big tables

> El 21 ago 2017, a las 10:00, Mariel Cherkassky > escribió: > > To summarize, I still have performance problems. My current situation : > I'm trying to copy the data of many tables in the oracle database into my > postgresql tables. I'm doing so by running