-2

I have a python app called 'Links' where users add social posts called 'publicreplies'.

This app sees decent traffic - the table containing publicreplies has grown quite large over the past 12 months (~83M rows and counting).

One rudimentary SELECT query on the links_publicreply table is showing up in slow_log. It's taking longer than 500ms, and is ~10X slower than what I'm experiencing in most other postgresql operations.

The query is as follows: select count(*) from links_publicreply where submitted_on >= current_date - interval '1 day';. Is basic enough.

The EXPLAIN ANALYZE results of this are here https://explain.depesz.com/s/RJ9b

Here's the output of \d links_publicreply:

                                      Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers                            
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
 category        | character varying(20)    | not null
 seen            | boolean                  | not null
 abuse           | boolean                  | not null
 device          | character varying(10)    | default '1'::character varying
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_report" CONSTRAINT "links_report_which_publicreply_id_fkey" FOREIGN KEY (which_publicreply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_seen" CONSTRAINT "links_seen_which_reply_id_fkey" FOREIGN KEY (which_reply_id) REFERENCES links_publicreply(id) DEFERRABLE INITIALLY DEFERRED
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

The hardware this is operating on has 8 cores and 60 GB memory. The postgreql DB shares this machine with a Django (python) app. I've been monitoring the server's performance and I don't see bottlenecks there.

Is there any way I can improve this query's performance? Being an accidental DBA of sorts, it'll be great to get advice on all options (if any) I have here. My overall goal is to deprecate old rows (e.g. older than 4 months) from the said table.

p.s let me know if you need more information to solve this

1
  • 3
    Why not just add an index on submitted_on? Commented Sep 12, 2018 at 15:34

2 Answers 2

2

The query is as follows: select count(*) from links_publicreply where submitted_on >= current_date - interval '1 day';. Is basic enough.

It also forces a table scan because the way I read your table definition, you have no index on the submitted_on field. If you do that type of query more often than once per day in the night - then, you know, indexing that field may help.

And while you talk about RAM.... you do not talk about discs. If you are running out of RAM (possible, could be a configuration issue) then you may hit the slowest discs of them all...

5
  • No, not running out of RAM, nor am I swapping to slower disc. Yes I don't have an index on that row; I actually haven't been needing to run this query. But to deprecate older data, I now need to isolate older rows and hence the query comes into play. So just to be sure, the best way forward right now is to add an index to the submitted_on row? Commented Sep 10, 2018 at 13:54
  • Yep, the difference is night and day. Although, what's the industry practice on creating a rarely used index on a large table (say, I want to invoke the index once a month or quarter for maintenance reasons)? Would be great to hear your opinion on that. Commented Sep 10, 2018 at 14:18
  • 1
    Well, it depends on use case. In your current use case I would have a nightly updated totals table (or one that is maintained by trigger) and never ask the question to the db. But it really depends on use case.
    – TomTom
    Commented Sep 10, 2018 at 14:33
  • 3
    @HassanBaig I can't speak for the whole industry, but why do you care if a query you run once a month takes 40 seconds?
    – jjanes
    Commented Sep 10, 2018 at 14:48
  • 3
    Try a BRIN index on that column Commented Sep 10, 2018 at 18:33
-2

I have had a similar issue(count(*) on a table with 10 colums an 700,000 rows...

here is a simple workaround

with Pool_Count as
(select epc.pool_id, count (pool_id)
from epc
group by epc.pool_id)

select * from pool_count where pool_id >1000

shortens the processing time massively because this subquery avoids the system searching through all columns and all rows for uniques to count

0

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