Pub date sorting/filtering causes slow OPAC search

[Chris Sharp]

OPAC searching and sorting by publication date is resulting in very slow searches. Core query SQL from an example search sorting on pub date that took 62 seconds on the database is attached. EXPLAIN ANALYZE to follow.

Evergreen 2.7.2
OpenSRF 2.4.0
PostgreSQL 9.3
Ubuntu LTS

[Chris Sharp (chrissharp123)]

Adding a pubdate filtering example...

[Mike Rylander (mrylander)]

Chris, I notice a couple things from the first explain: it looks like you have a superpage size of 10k, rather than the more common 1k or 2.5k; it looks like you've forced a search with no terms, just a sort by pubdate for all "book-ish" things. The latter is the main reason for the slowness, though the sort doesn't help it go faster.

With the new attribute infrastructure, we have the ability to build smarter "show everything" listings (rather than using the full power of QP-based search), so I think there's an opportunity for a more traditional (in the library sense) "index listing" API which would be fairly simple (comparatively) when there are no user-provided search terms, just filters and the like.