2014-08-29 7:28 GMT+03:00 Burgess, Freddie :
>-> Index Scan using idx_clone_report_query_y201408 on
> SARS_IMPACT_REPORT_y2014m08 this__2 (cost=0.57..5570.95 rows=103
> width=136) (actual time=8.300..33308.118 rows=710202 loops=1)
> Index Cond: ((model_uid =
Any suggestions on a query rewrite to speed this poor performing query up.
work_mem=164MB
Thanks
explain (analyze on, buffers on) select * from SARS_IMPACT_REPORT this_
where this_.model_uid=1
and this_.source_date_time between '2014-08-08 19:21:08.212'::timestamp
without time zone and '2
On 08/28/2014 01:50 AM, gmb wrote:
> Can somebody please confirm whether aggregate functions such as GROUP BY
> should use indexes ?
Sometimes. In your case, the index has one more column than the GROUP
BY, which makes it less likely that Postgres will use it (since
depending on the cardinality
Hello All,
I learned a lot by inputs from all of you. To share one more thing about
java_JDBC bypassing autocommit that I tried:
(1) Read/save source data into f1.csv, f2.csv, ..
(2) Copy/load into dest psql.DB
CopyManager cm = null;
FileReader fileReader = null;
2014-08-28 14:29 GMT+03:00 gmb :
> Unfortunately , I don't have a lot of memory available ( 65 connections ,
> work_mem = 64MB in pg conf ).
>
You don't have to change cluster-wide settings here.
You can issue `SET` command from your client right before running your
query, only your session will
Thanks for these suggestions
Unfortunately , I don't have a lot of memory available ( 65 connections ,
work_mem = 64MB in pg conf ).
>> I think index will be of no help here, as (1) you're reading whole table
>> anyway and (2) `amount` is not part of your index.
I did not think that the the fie
On Thu, Aug 28, 2014 at 11:50 AM, gmb wrote:
> Can somebody please confirm whether aggregate functions such as GROUP BY
> should use indexes ?
Yes, if the planner deems it faster than other approaches. It can make
wrong choices for many reasons, but usually when your planner tunables
like random_
2014-08-28 12:08 GMT+03:00 gmb :
> GroupAggregate (cost=303425.31..339014.43 rows=136882 width=48) (actual
> time=4708.181..6688.699 rows=287268 loops=1)
> Buffers: shared read=23899, temp read=30974 written=30974
> -> Sort (cost=303425.31..306847.34 rows=1368812 width=48) (actual
> time=47
> Can you send `EXPLAIN (analyze, buffers)` for your query instead?
> It'll show exactly what's going on.
GroupAggregate (cost=303425.31..339014.43 rows=136882 width=48) (actual
time=4708.181..6688.699 rows=287268 loops=1)
Buffers: shared read=23899, temp read=30974 written=30974
-> Sort
2014-08-28 11:50 GMT+03:00 gmb :
> It seems as if the planner is not using the PRIMARY KEY as index which was
> my assumption.
>
Can you send `EXPLAIN (analyze, buffers)` for your query instead?
It'll show exactly what's going on.
--
Victor Y. Yegorov
Hi all
I have the following table with 10+ million records:
create table ddetail (
ddet_id serial,
co_id integer,
client_id integer,
doc_no varchar,
line_id integer,
batch_no integer,
amount NUMERIC ,
...,
constraint PRIMAR KEY ( co_id , client_id , doc_no , line_id, ddet_id )
) ;
When doing
11 matches
Mail list logo
